Excel: SVERWEIS (VLOOK UP) knifflig!

Mein Problem:

Tabellenkopf

Lieferant Produkt Produkt-Nr. Menge Betrag

In Spalte 1 habe ich ein Listenfeld (Drop-Down Feld) mit mehreren Lieferanten, aus denen ich waehlen kann.

Jeder Lieferant bietet mehrere Produkte an.

Wenn ich in Spalte 1 "Lieferant F" waehle, dann soll in Spalte 2 eine Drop-Down Liste mit der Produktpalette von Lieferant F erscheinen, aus der ich ein "Produkt S" auswaehlen kann.

Dementsprechend soll die Produktnummer in Spalte 3 erscheinen.

Und nach Eingabe der Menge schliesslich der Betrag. (Generiert aus Menge und zugehoerigem Produktpreis.)

Reichen hier Funktionen oder braucht man da Makros?

Wer von euch kann helfen??

Beispiel:

L(A) P1 1323 5EUR
P2 4930 9EUR
P3 5461 2EUR
L(B) PA 54 2EUR
PB 70 6EUR
LC …

 

Hi,

das geht ohne VBA.

Für das Erstellen der Abhängigkeiten der DropDown-Listenfelder findest du auf meiner HP, Seite "Beispiele o. VBA" das Beispiel DropDow abhängig.

Die Produktnummer und den Preis für das Produkt kannst du mit SVERWEIS() oder INDEX() erhalten, musst nur eine Tabelle haben, in der in einer Spalte die Produktnamen, in einer die Produktnummer und in einer dritten der Produktpreis stehen.

Den Gesamtpreis je Produkt erhältst du durch einfache Multiplikation der Zelle mit dem Preis mit der Zelle, in der die Anzahl steht.

 

Hallo Karin,

die von dir vorgeschlagene Variante funktioniert.

Leider habe ich nicht nur 3 Lieferanten, sondern 60+. Dass heisst die Verschachtelung in der =WENN Funktion ist ziemlich lang. Vor allem, weil man den langen Lieferantennamen eingeben muss. Kann man denn die Lieferanten nicht ueber kurze Codes definieren?

Und dann ist da die Sache mit den Leerzeichen. Die Lieferantennamen haben ja nicht alle die Struktur eines Wortes (richtig waere "Luxol Eyewear Merchants" und nicht "LuxolEyewearMerchants"). Wenn ich aber Leerzeichen verwende, stimmt die Zuordnung nicht mehr.

Ausserdem erscheint in der zweiten Spalte neben dem (eigentlich interessanten) Produktnamen immer der Lieferantenname. Man geht dabei auf Nummer sicher, wenn man das Produkt auswaehlt, aber die Spalte "Produkt" wird dann unheimlich breit.

Also lassen sich hier irgendwo Kuerzel einbauen?
(Ich werde das mal versuchen.)

Danke im Voraus!

Markus

 

Problem geloest.

 

Siehe naechste Antwort.

 

Problem geloest.

 

[/B]

Problem liesse sich so loesen:

Lieferant / Code
Luxol Eyewear Merchants / L001 (=SVERWEIS)

Code fuer Tabelle mit den jeweiligen Produkten
P001

Damit liesse sich die =WENN Funktion enorm kuerzen.

Trotzdem dauert es lange, alle 60+ LieferantenCodes und die zugehoerigen ProduktTabellenCodes in die Formel einzutippen.

Gibt es vielleicht eine besser Loesung (im Sinne von einfacherer Dateneingabe)?

 

Hi Markus,

ich kenne deine derzeitige Arbeitsmappe nicht, da lässt sich schwer etwas dazu sagen. Lade doch die Mappe (mit ein paar Spieldaten) mal hoch.

 

Hier die Mappe.

Markus

 

Hi Markus,

ich hatte noch eine andere Methode im Auge, aber die funktioniert leider nicht, da du die Bereiche in Abhängigkeit von ihrer Anzahl dynamisch gestaltest hast.
Es ließe sich aber sicher mit VBA lösen.

 

Hi Karin.

Schau dir mal diese Loesung von mir an. Geloest ueber 'abhaengiges Gueltigkeitsdropdown-Feld' unter Verwendung von 'Namen'.
Habe fuer jeden Lieferanten einen Code eingefuert, um die Namenvergabe zu vereinfachen.

Neues Problem:
Angenommen ich waehle in A12 einen neuen Lieferanten aus, merke aber nach der Dateneingaben, dass ich diesen gar nicht brauche. Nun habe ich versucht eine Art 'leeres Feld' einzufuegen, um alle Zellen in der Zeile wieder auf Null zu setzen. Da wo Funktionen standen, kommt die Meldung "#N/ A" oder Nullen. Ich moechte aber nur leere Felder. Also: wenn ich in A12 das 'Leerfeld' auswaehle, sollen alle restlichen Felder der Zeile auch automatisch leer werden.
Hast du Vorschlaege?

Ausserdem moechte ich alle Zellen ausser die rotgefaerbten schuetzen. Ich kriegs aber nicht auf die Reihe.

Und kann ich die Zellen A17 und A19 (trotz einer Spalte) zweiteilen/splitten?

Und wenn neue Lieferanten und Produkte hinzukommen, muessen alle Tabellen wieder veraendert werden. Laesst sich da nicht etwas dynamisches machen?

Erkennst du den Sinn der Mappe? Die jenigen, die das Template verwenden, sollen keine Daten aendern, sondern nur Daten auswaehlen (und eingeben) koennen.

Hier die Mappe:

 

@Deltafox

Ich kann leider Deine ZIPs nicht öffnen, keine Ahnung warum > invalid string...
Dein Problem interessiert mich auch für eine eigene Anwendung, ich hatte deshalb nach Deinen Vorgaben getestet.

Das Hauptproblem ist für mich die grosse Datenbasis, sie ist fremdbestimmt und deshalb etwas "dynamisch". Das erzwingt laufende Pflege und Anpassungen. Feste Zellbezüge zu verarbeiten kommt für mich so nicht in Frage - das wäre eine perfekte Sysiphusarbeit!

Schau Dir meinen Versuch an. vielleicht kannst Du etwas davon weiterverwenden.
In Gebrauch habe ich diesen Entwurf noch nicht, es könnten also schon noch Haken und Verbesserungsmöglichkeiten drin sein, und die Darstellung ist auch erst skizzenhaft...

Die Blätter sind getrennt in Ausgabe und Datenbasis. Sie haben keinen direkten Bezug untereinander, sie können also unabhängig voneinander geändert werden, Erst das Eingabeformular mit den VBA-Makro stellt den Zusammenhang her.

Gruss Urs

 

@ Urs2

http://www.siteupload.de/p738622-templatebetterzip.html

(Das wird dir auch nicht weiterhelfen.)
Aber wie soll ich's sonst hochladen?

 

@ Urs2

Kann dein Dokument auch nicht oeffnen.

 

..........

 

Hi Markus,

dadurch, dass du jetzt feste Bereiche für die einzelnen Produkte verwendest, kann sich Daten Gültigkeit mit INDIREKT() auf die Ausgangszelle in Spalte A beziehen, und das war nicht möglich, als du BEREICH.VERSCHIEBEN() verwendet hast - genau das war der andere Lösungsweg, den ich auch ins Auge gefasst hatte, aber aus besagtem Grund nicht gangbar war. Dennoch - es gibt doch eine Möglichkeit, den Wertebereich dynamisch zu gestalten, z.B.:
CompanyA =INDIREKT("$H$3:"&ADRESSE(ANZAHL2(Sheet1!$H$3:$H$14)+2;8)),
CompanyB =INDIREKT("$H$3:"&ADRESSE(ANZAHL2(Sheet1!$H$3:$H$14)+2;9)) usw.

Ich würde das "leere" Feld weglassen, denn bei einer Falschauswahl muss der Lieferant in Spalte A einfach nur gelöscht (Entf.-Taste) werden, damit die Zelle wieder leer ist.
Damit keine 0 oder #N/A erscheinen schreibe deinen Formeln nach diesem Prinzip (z.B. für C6): =WENN(ISTFEHLER(SVERWEIS(B6;products!C$3: D$30;2;FALSCH));"";SVERWEIS(B6;products!C$3: D$30;2;FALSCH)). In Spalte C war übrigens noch ein genereller Fehler - du hatest die $ vor den Zeilennummern vergessen.

Zellen schützen: rote Zellen markieren -> Format -> Zellen -> Reiter: Schutz und gesperrt deaktivieren. Anschließend das Tabellenblatt schützen.

Eine Zelle innerhalb einer Spalte lässt sich nicht splitten. Du kannst nur eine neue Spalte A einfügen und die Lieferantenauswahl in die neue Spalte A verschieben. Den Rahmen für die Zellen mit den Lieferanten nimmst du halt über Spalte A und B.

Wenn neue Lieferanten/Produkte hinzukommen sollen dann muss der betreffende Tabellenbereich nach unten offen sein. Aus diesem Grund würde ich die Tabelle für die Produkte anders aufbauen, im Prinzip so ähnlich wie du das in deiner ersten Arbeitsmappe hattest - für jeden Lieferanten eine eigene Teiltabelle.

 

@deltafox

Da scheint mein Packer wohl das Zipperlein zu haben...
Ich versuche es mal so, grössenmässig geht es gerade noch, nimm dann die TXT-Endung einfach weg.

Gruss Urs

 

@ Urs2:

Hier meine Datei. Auch einfach umbenennen.

 

@Urs2: Du konntest meine Datei oeffnen?

Liesse sich deine Anwendung (generell) auf mein Dokument uebertragen?
(Ich bin ein bisschen begeistert von deiner Umsetzung. Wirklich gut.)

Ich braeuchte dann automatisch 100 Tabellenreiter, da ich 100 Unternehmen habe. Schafft das Excel?

 

Klar, wie hätte ich sonst die gesperrten Formeln anschauen können? Und damit gleich zu Deinem zweiten Thread >
Du willst die Zusammenstellung als geschützte XLS-Datei speichern, damit sie nicht nachträglich manipuliert werden kann. Vergiss es, das wäre nur Augenwischerei!
Speichere sie als PDF, dann ist sie einigermassen geschützt. Eine Kopie zum internen Weiterverarbeiten kann immer noch als XLS abgelegt werden...
Das Nummerieren, Datieren und Speichern liesse sich mit VBA-Code erledigen. Ein Button im Blatt kann den Abschluss-Code aufrufen - wenn alles fertig ist (nicht vorher, sonst wird produzierter Ausschuss auch nummeriert...).

Zu diesem Thread:
Excel2003 > Sheets in a workbook = Limited by available memory > http://office.microsoft.com/en-us/excel/HP051992911033.aspx
Ob so viele Sheets sinnvoll sind, hängt von der Datenmenge ab, und von der Art wie die Daten in das Blatt kommen.
Man könnte auch mehrere Lieferanten auf einem Blatt zusammenfassen, sie haben dann die gleiche Kürzel-Nummer.
Bedingungen wären:
- Gleicher Artikelname nur einmal auf einem Blatt (nach dem wird ja gesucht)
- Leerzeilen zwischen Lieferanten zur Uebersichtlichkeit sind OK, aber in Spalte A muss dort irgendetwas stehen, sonst bricht die Suche nach der ersten Gruppe ab...

Generell liesse sich das VBA für Deine Daten schon verwenden. Hauptbedingung ist, dass die Nutzdaten für alle 100 Lieferanten in der gleichen Reihenfolge, Form und in jedem Blatt am gleichen Ort vorliegen. Einen eigen Code für jedes Blatt... Nein!
Ich höre schon gewisse Leute, die jetzt sagen, dafür wäre doch die Access-Datenbank das richtige. Das kann gut sein, aber damit kenne ich mich nun gar nicht aus...

Da ich die kompletten Zeilen in der Zusammenstellung einfüge, könnten auch Deine Fusszeilen bleiben, sie rutschen dann einfach mit jedem Eintrag weiter hinunter...

Hast Du den Code angeschaut? Wie Du ihn anpassen und erweitern könntest?
Auf die Schnelle hatte ich ihn noch nicht kommentiert, wenn Du willst, kann ich die Kommentare noch einfügen - so ganz eingängig ist ein fremder Code ja nie... aber nicht mehr heute...

Gruss Urs