Excel - DropDown, Indirekt, usw über mehrere Tabellenblätter?
Hallo,
ich habe aktuell in der Zelle B6 ein DropDown mit einer Länderauswahl. Das hab ich zumindest geschafft 😅 Ich möchte jetzt in B7 eine DropDown Liste der möglichen PLZ Regionen, je nachdem welches Land in B6 ausgewählt wurde.
Für jedes Land in dieser Liste gibt es ein eigenes Tabellenblatt. Die Tabellenblätter haben den entsprechenden Ländernamen. Dort sind in den Zellen B1:D1 PLZ Regionen.
Mit "Indirekt" bekomm ich leider nur einzelne Werte, aber kein DropDown.
Versteht jemand mein Anliegen und weiß eine Lösung?
3 Antworten
Hier ist ein Ansatz mit VBA - etwas Besseres fällt mir im Moment nicht ein. Begründung unten :)
Ein Makro, das basierend auf dem Wert in A1, ein DropDown-Feld in B1 mit den entsprechenden Werten erzeugt:
Sub getPostalCodes()
Dim countryCode As String
countryCode = Cells(1, 1).Value
Dim range As range
Dim Worksheet As Worksheet
Set Worksheet = ThisWorkbook.Sheets("Tabelle1")
If countryCode = "AT" Then
Set range = Worksheet.range("B20:B24")
End If
If countryCode = "CH" Then
Set range = Worksheet.range("C20:C24")
End If
If countryCode = "DE" Then
Set range = Worksheet.range("A20:A24")
End If
With Worksheet.range("B1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & range.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Damit das Makro automatisch ausgeführt wird, wenn du den Wert in A1 änderst, benötigst du die folgende Funktion in deinem Arbeitsblatt:
Sub Worksheet_Change(ByVal Target As range)
If Target.Address = "$A$1" Then
Call getPostalCodes
End If
End Sub
Alles passiert im Moment in einem einzigen Arbeitsblatt.
Vielleicht schaffst du es, das Schnipsel an deine Bedürfnisse anzupassen. Falls nicht, gerne melden :)
Mir scheint dein Vorhaben ziemlich dynamisch zu sein, weshalb ich (als Programmierer) zu VBA greifen würde - vor allem, weil du ein Element (DropDown-Feld) erzeugen möchtest, das man mit einer Formel nicht erzeugen kann.
dazu kann man das sogenannte bedingte Drowdown nutzen, Ansätze gibt es dazu mehrere, aber ich finde diese Varianten hier recht einfach, denn es benötigt eigentlich nur mehrere intelligente Tabelle und die Funktion indirekt, die es schon seit sehr langer Zeit in Excel gibt.
Hier mal ein grobe Übersicht, wie dies realisiert werden kann.
Die Tabelle Land enthält entsprechend alle Länder die im ersten Dropdown auftauchen sollen. Die anderen Spaltenköpfe müssen exakt genauso heißen, wie sie auch in der Land Tabelle heißen und jede muss für sich ebenfalls als intelligente Tabelle formatiert sein, dies geht indem man diese gesamte Tabelle als Deutschland bis 90530 markiert und strg+t drückt, dann wird man noch gefragt, ob die Tabelle Spaltenköpfe hat, wenn nicht bereits angehakt, dann einfach mit dem haken bestätigen.
Sobald die Tabelle angelegt ist wird diese so farbig wie in meine Bild. nun gibt es direkt nach Hilfe einen weiteren Reiten Tabellenentwurf, dort draufklicken und ganz links unter Tabellennamen, diese so nennen, wie auch das Land heißt, was diese Tabelle abbildet.
Die Tabelle Land kann dabei heißen wie sie will, in meinem Falle heißt sie Länder, du musst dir diesen Namen nur merken, denn den Namen brauchst du gleich.
unter Land (B3) befindet sich ein Dropdown, dort habe ich unter Daten -> Datentools Datenüberprüfung -> Liste folgendes eingegeben:
=indirekt("Tabellenname")
in meinem Falle lautet der Tabellenname Länder also
=indirekt("Länder")
in der Zelle C3 kannst du dir nun zunutze machen, dass alle Tabellen genauso heißen, wie die Länder in der Tabelle Land und gehst wieder in Daten -> Datentools -> Datenüberprüfung -> Liste gibst einfach ein
=indirekt($B$3)
ggf. taucht nach dem bestätigen eine vermeintliche Fehlermeldung auf, die kann aber einfach bestätigt werden.
und schon ist alles fertig.
Die erste Liste kann man auch wie unten von @maexchen vorgeschlagen statisch verlinken, Nachteil ist, dass man diese dann jedes mal neue verlinken muss, wenn neue Einträge hinzu kommen, hier erweitert sich der Bereich automatisch, wenn ich ein neues Land hinzufüge, ich muss dann nur logischerweise auch eine neue Tabelle für dieses Land hinzufügen, andernfalls ist das 2 Dropdown unter PLZ leer.
Anmerkung
Leider unterstützt Excel im Dropdown Listenfeld keine kombinierten indirekt eingaben, sonst könnte man die gesamte Tabelle in denen die Länder inklusive PLZ stehen als eine intelligente Tabelle definieren mit dem Namen Länder und dann z.B.
=indirekt("Länder[#Kopfzeilen]")
sich im ersten Dropwdown alle Länder abholen - das funktioniert noch
aber leider scheitert
=indirekt("Länder["&B3&"]")
gibt man dies als Formel ins Tabellenblatt ein funktioniert es ohne Problem aber als Listenfeld geht es leider nicht, ich hoffe hier bessert Excel ggf. noch nach. Dies würde das ganze deutlich dynamischer machen, da nur noch eine Tabelle gepflegt werden muss.
Schau mal, ob Dir das weiterhilft.
https://www.youtube.com/watch?v=HX5zIqDjQBA&ab_channel=AndreasThehos
https://www.youtube.com/watch?v=ru9IQqaCJc8&ab_channel=AndreasThehos