Hallo zusammen,
es existieren in Excel 2 eigenständige Dateien und zwar einmal die Datei „Kundenartikellisten“ für die einzelenen Kunden (jeweils 1 Tabellenblatt) und die Datei „Artikelstammdaten“. In den Artikelstammdaten sind in der Spalte A die Artikelnummern gespeichert und in mehreren Spalten auch die Verkaufspreise der jeweiligen Kunden hinterlegt, z.Bsp. in Spalte U die VK-Preise für den Kunden 10001. Die Kundennummer ist dabei als Spaltenüberschrift in der Spalte U eingetragen.
Es soll jetzt durch die Eingabe der Kunden-Nr.in der Kundenartikelliste (z.Bsp. 10001 immer im Feld G10) nur die Artikel aus dem Artikelstamm selektiert werden, wo ein VK-Preis ( in diesem Fall in Spalte U) hinterlegt worden ist. Dabei soll der VK-Preis in Spalte D und die dazugehörende Artikelnummer in Spalte A der entsprechenden Artikelliste durch Formeleingabe automatisch eingetragen werden. Die restlichen Daten bekomme ich dann über die Verbindung zur Artikelnummer in die restlichen Felder der Artikelliste.
Mit welcher Formel kann ich bei Excel den Verkaufspreis und die dazugehörende Artikelnummer aus dem Artikelstamm filtern und diese Daten in die entprechende Kundenartikelliste übernehmen?
Für hilfreiche Unterstützung wäre ich sehr dankbar.
Gruß
Hermann
Hallo Hermann,
im Prinzip kann man das mit Formeln lösen. Wenn es sehr viele Artikel und viele Kunden sind, dann wird die erforderliche Berechnungszeit in der Kundenartikelliste relativ groß.
Dann wird eine Lösung per Makro ggf. handlicher.
Gruß
Franz
Beispiel: Blatt in Kundenartikelliste
Tabellenblattname: Kunde A
A B C D E F G H I
1 Kunde Kunde A
2 Adresse Zusatz EK
3 Straße Teststr 1
4 PLZ 12345 Ort Testdorf
5
6 Ansprchpartner Frau M. Test Hilfswerte
7 Tel 01234-12345 E-Mail [email protected] Anzahl Artikel 2
8 Fax 01234-12346 Max.Zeilen 14
9 Spalte-Knd. 21
10 Artikel-Nr VK-Preis Kunden-Nr 10001 Zeile
11 A00111 B001 C00001 205,00 2
12 A124322 B003 C00003 345,00 4
13 0 0 0 0,00 14
Benutzte Formeln:
A11: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H11-1;0)
A12: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H12-1;0)
A13: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H13-1;0)
B11: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H11-1;1)
B12: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H12-1;1)
B13: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H13-1;1)
C11: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H11-1;2)
C12: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H12-1;2)
C13: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H13-1;2)
D11: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H11-1;$I$9-1)
D12: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H12-1;$I$9-1)
D13: =BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammZelleA1;$H13-1;$I$9-1)
H11: =KKLEINSTE(WENN(BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammArtikelNr;0;$I$9-1)\>0;ZEILE(Artikelstammdaten.xlsx!StammArtikelNr);$I$8);ZEILE(H1))
H12: =KKLEINSTE(WENN(BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammArtikelNr;0;$I$9-1)\>0;ZEILE(Artikelstammdaten.xlsx!StammArtikelNr);$I$8);ZEILE(H2))
H13: =KKLEINSTE(WENN(BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammArtikelNr;0;$I$9-1)\>0;ZEILE(Artikelstammdaten.xlsx!StammArtikelNr);$I$8);ZEILE(H3))
I7: =ZÄHLENWENN(BEREICH.VERSCHIEBEN(Artikelstammdaten.xlsx!StammArtikelNr;0;$I$9-1);"\>0")
I8: =ZEILE(Artikelstammdaten.xlsx!StammZelleA1)+ZEILEN(Artikelstammdaten.xlsx!StammArtikelNr)+1
I9: =VERGLEICH($G$10;Artikelstammdaten.xlsx!TitelZeile;0)
Bitte beachten: Die Formeln in Zelle H11 bis H13 sind Matrixformeln. Eingabe der Formeln mit Tastenkombination Strg+Shift+Enter abschliessen.
Blatt mit Artikelstammdaten. Einige Zellbereiche, die in den Kundenartikellisten-Formeln verwendet werden sind mit einem Namen versehn. Kein Muss, aber macht Formeln ggf. leichetr lesbar.
Tabellenblattname: Tabelle1
A B C D E F G H I J K L M N O P Q R S T U
1 Artikel-Nr Wert 1 Wert 2 Wert 3 9985 9986 9987 9988 9989 9990 9991 9992 9993 9994 9995 9996 9997 9998 9999 10000 10001
2 A00111 B001 C00001 D001 205,00
3 234561 B002 C00002 D002 123,00
4 A124322 B003 C00003 D003 234,00 345,00
Namen in der Tabelle:
StammArtikelNr: =Tabelle1!$A$2:blush:A$13
StammZelleA1 : =Tabelle1!$A$1
TitelZeile : =Tabelle1!$1:blush:1