Dropdown mit Sverweis

07.05.2021 13:40:54

Helmut

Einen schönen Vormittag, ich habe eine Datei angehängt wo ich folgendes Problem habe: im TB Daten gebe ich in Spalte PLZ die Postleitzahl ein. Durch einen Sverweis wird mir in Spalte Ort der dazugehörige Ort hinzugefügt. Wenn man jetzt im Tabellenblatt PLZ-Ort-Bezirk nachsieht, sieht man, dass es hier für diese Postleitzahl mehrere Ortschaften gibt. Was ich jetzt möchte ist, dass man wenn man die Postleitzahl eingibt in der Spalte Ort ein Dropdown Menü hat, um den richtigen Ort auszuwählen. Wäre das irgendwie machbar? Besten Dank im voraus, Helmut
https://www.herber.de/bbs/user/146055.xlsm

AW: Dropdown mit Sverweis

07.05.2021 13:46:23

Hajo_Zi

https://www.herber.de/excelformeln/pages/SVERWEIS_bei_mehrfach_vorkommendem_Suchkriterium.html
Grußformel

AW: Dropdown mit Sverweis

07.05.2021 13:55:05

Helmut

Servus Hajo, herzlichen Dank für die rasche Antwort, es sollte jedoch so sein, dass ich zum Beispiel in Zelle F3 solch ein Dropdownmenü habe wie bei der Datenüberprüfung. Ist das nicht möglich oder?

AW: Dropdown mit Sverweis

07.05.2021 14:05:33

Hajo_Zi

wo ist das Problem. Der Suchbegriff steht in F3
Gruß Hajo

AW: Dropdown mit Sverweis

07.05.2021 14:16:05

Hajo_Zi

Daten

	H	I	J	K	L
3	4714	Breinroith	Breitwies	Bruckhof	Egg

Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle	Formel	Bereich	N/A
I3	{=INDEX('PLZ-ORT-Bezirk'!$C:$C;KKLEINSTE(WENN('PLZ-ORT-Bezirk'!$B$1:$B$46=$H$3;ZEILE(X$1:X$46));SPALTE()-8))}	$I$3
J3:L3	{=WENNFEHLER(INDEX('PLZ-ORT-Bezirk'!$C:$C;KKLEINSTE(WENN('PLZ-ORT-Bezirk'!$B$1:$B$46=$H$3;ZEILE(Y$1:Y$46));SPALTE()-8));"")}	$J$3

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

definierte Namen
Name	Bezieht sich auf	Tabelle	Z1S1-Formel
PLZ	='PLZ-ORT-Bezirk'!$B$1:$D$46		='PLZ-ORT-Bezirk'!R1C2:R46C4

Schriftformate
Zelle	Rot	Grün	Blau	Color	Stil	Unterstreichung	Effekte	Durchgestrichen	Schriftart
H3:L3	0	0	0	0					Calibri

Zellen mit Schriftformatierung automatisch werden nicht dargestellt

Formatierte Tabellen (Daten)
Bereich	Name	Vorlage	Vorlage orig.
$B$2:$BA$20	Tabelle1	Tabellenformat - Mittel 2	TableStyleMedium2

Gruß Hajo

AW: Dropdown mit Sverweis

07.05.2021 14:11:36

Klaus

Hallo Helmut,
nimm doch die Datenüberprüfung.
Zulassen: Liste
Quelle:


=INDIREKT("'PLZ-ORT-BEZIRK'!C"&VERGLEICH(E3,'PLZ-ORT-Bezirk'!B:B,) &":C"&-1+VERGLEICH(E3,'PLZ-ORT-Bezirk'!B:B,)+ZÄHLENWENN('PLZ-ORT-Bezirk'!B:B,E3))

Leider akzeptiert die Datenüberprüfung kein Index:Index, aber mit der volatilen INDIREKT Formel geht es.
Ich gehe davon aus, deine Originalliste ist länger - aber ebenfalls sortiert, da die Liste in deiner Beispieldatei sortiert ist. In einer unsortieren Liste funktioniert das natürlich nicht.
LG,
Klaus M.

.....noch nicht ganz

07.05.2021 14:32:35

Helmut

Hajo, ich sehe mir deinen Lösungsvorschlag anschließend an. Nun zu dir Klaus M., ich habe deine Anweisungen befolgt, bekomme jedoch eine Fehlermeldung, dass es mit dieser Formel ein Problem gibt. Ich gehe davon aus, dass du es versucht hast. Kannst du mir eventuell die Beispiel Datei senden oder hochladen? Besten Dank im Voraus, Helge Helmut

AW: .....noch nicht ganz

07.05.2021 14:42:11

Klaus

Hallo Helmut,
vermutlich hast du nicht die ganze Formel kopiert und ein "Klammer zu" vergessen oder so.
Hier der Upload als Beweis dass es funktioniert:
https://www.herber.de/bbs/user/146057.xlsm
Hajo's Lösung musst du dir nicht ansehen, er hat deine Aufgabe falsch verstanden. Seine Lösung listet die Orte "von links nach rechts" neben deiner Tabelle auf, anstatt ein Dropdown zu erzeugen.
LG,
Klaus M.

AW: Dorpdown mit INDEX() und VERGLEICH() ...

07.05.2021 14:41:01

neopa

Hallo Helmut,
... definiere bei aktivierter Zelle F4 folgende Formel im Namensmanager und benenne diese z.B.: _Ort

=INDEX('PLZ-ORT-Bezirk'!$C:$C;VERGLEICH(E3;'PLZ-ORT-Bezirk'!$B:$B;0)):INDEX('PLZ-ORT-Bezirk'!$C:$C; VERGLEICH(E3;'PLZ-ORT-Bezirk'!$B:$B;0)+ZÄHLENWENN('PLZ-ORT-Bezirk'!$B:$B;E3)-1)
Und dann in der Datenüberprüfung Zulassen: Liste und Quelle:_Ort
Gruß Werner
.. , - ...

AW: Dorpdown mit INDEX() und VERGLEICH() ...

07.05.2021 14:44:27

Klaus

Danke Werner, ich wusste es ging irgendwie mit INDEX:INDEX :-) Den Namensmanager hatte ich nicht auf dem Schirm.
Helmut - nimm Werners Lösung! Meine Formel ist Volatil, das bedeutet wenn du viele Einträge hast kann die Tabelle langsam werden. Werners Formel hat dieses Problem nicht.
LG,
Klaus M.

AW: Dorpdown mit INDEX() und VERGLEICH() ...

07.05.2021 15:07:25

Helmut

Erstmals recht herzlichen Dank euch beiden, mit der Lösung von Klaus M. bin ich recht zufrieden, Dankeschön. Bei der Lösung von Werner habe ich dann zum Beispiel in Zelle F4 "#Überlauf!" stehen und das zieht sich bis Celle F7 in der angeführten Beispiel Datei. Habe jedoch deine Anweisung genau befolgt. Wo liegt hier das Problem? LG Helmut

AW: kann ich so nicht nachvollziehen ...

07.05.2021 15:14:35

neopa

Hallo Helmut,
... kannst Du Deine Datei mit diesen Fehler hier mal einstellen?
Gruß Werner
.. , - ...

AW: kann ich so nicht nachvollziehen ...

07.05.2021 15:19:01

Helmut

Servus Werner, habe nochmals die Datei hochgeladen! lg Helmut
https://www.herber.de/bbs/user/146058.xlsm

AW: ein Schreibfehler meinerseits ...

07.05.2021 15:31:04

neopa

Hallo Helmut,
... hat Dich veranlasst die benannte Formel nicht wie von mir geplant zu erstellen. Anstelle "definiere bei aktivierter Zelle F4" sollte es korrekt lauten "... F3. Sorry. Da Du es schon so definiert hast, musst Du jetzt im Namensmanager in der Formel zu _Ort (bei aktivierter Zelle F3!) nur ... E2... in ... E3 ... ändern und natürlich die Zellformeln =_Ort löschen. Dort willst Du ja über die Dropdownzelle erst die Auswahl treffen.
Gruß Werner
.. , - ...

....Alles Bestens! Dankeschön

07.05.2021 15:36:02

Helmut

Jetzt ist alles perfekt! Dankeschön Werner, Dankeschön Klaus M. und auch an Hajo. Ein schönes Wochenende wünsche ich, Dankeschön nochmals

AW: ....Alles Bestens! Dankeschön

07.05.2021 15:37:38

Hajo_Zi

offen bedeutet es soll noch eine Antwort kommen.
Warum ist dein Beitrag Offen.
Du willst doch was machen. Soll jemand vorbei kommen?
Das ist nur meine Meinung zu dem Thema.
Grußformel

AW: bitteschön owT

07.05.2021 15:38:37

neopa

Gruß Werner
.. , - ...

Dropdown mit Sverweis

Forumthread: Dropdown mit Sverweis

16
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Dropdown mit Sverweis

Forumthread: Dropdown mit Sverweis

16 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

16
Beiträge zum Forumthread

Beiträge zu diesem Forumthread