Drop-down-Gültigkeitslisten miteinander verknüpfen mit INDIREKT()

Gültigkeitslisten in Excel sind eine feine Sache: Per Dropdown-Liste wird einfach alles vorgegeben, was man eingeben darf bzw. soll. Das Erzeugen einer einfachen Gültigkeitsliste ist mit wenigen Kniffen bewerkstelligt. Falls jedoch eine zweite Gültigkeitsliste ins Spiel kommen soll, die in Abhängigkeit der getroffenen Auswahl in der ersten Gültigkeitsliste nur ganz spezielle Einträge anzeigen soll, muss die Trickkiste aufgemacht werden. Wie das Ganze funktioniert, zeigen wir Ihnen hier.

icon_autor Autor: Hartmut Erb

Step by Step

1. Im nachfolgenden Beispiel sollen 2 Drop-down-Gültigkeitslisten miteinander verknüpft werden. Sobald aus der ersten Drop-down-Liste ein Eintrag ausgewählt ist, sollen die Inhalte der zweiten Drop-down-Liste dynamisch angepasst werden.

Konkret bedeutet dies, dass bei der Auswahl eines Staates die zugehörigen Landesniederlassungen im zweiten Drop-down-Menü zur Auswahl gestellt werden. Im ersten Schritt müssen Sie die Matrix für die Gültigkeitskriterien anlegen. In einer Spalte tragen Sie die benötigten Staaten ein. In den weiteren Kriterien-Spalten legen Sie die Länder mit den Städten fest, in denen sich die Landesniederlassungen befinden.

DropDownGueltigkeitslisten_01

Abb. 1: Die Ausgangstabelle beinhaltet die Zuordnungen der Niederlassungen zu den Ländern.

2. Im nächsten Schritt müssen Sie die Namen für die Staaten sowie für die Länder, in denen sich die Niederlassungen befinden, definieren. Legen Sie dazu folgende Namen mit den jeweiligen Zellbezügen an:

Name Bezieht sich auf
 
Staaten =Tabelle1!$A$7:$A$10
 
Frankreich =Tabelle1!$B$7:$B$10
 
 Italien =Tabelle1!$C$7:$C$10
 
Spanien =Tabelle1!$D$7:$D$10
 
England =Tabelle1!$E$7:$E$10

 

Den Dialog zur Namensdefinition rufen Sie über das Menü Formeln  -> Definierte Namen   ->  Namen definieren auf (Excel 2003: Einfügen  -> Namen -> Definieren).

Die Namensvergabe für die Staaten in Spalte A kann frei gewählt werden. Im Beispiel wird der Name "Staaten" verwendet. Unbedingt zu beachten ist, dass die Namen der Länder exakt mit den Überschriften in Zeile 6 übereinstimmen.

DropDownGueltigkeitslisten_02

Abb. 2: Hier werden die Namen festgelegt.

3. Nachdem alle Namen definiert sind, wählen Sie Zelle A3 aus, in der das erste Drop-down-Menü für die Landesauswahl erstellt werden soll.

4. Öffnen Sie über das Menü Daten  ->  Datentools  ->  Datenüberprüfung das Dialogfenster zur Erfassung der Gültigkeitskriterien (s. Abb. 3). In Excel 2003 wählen Sie Daten  ->  Gültigkeit.

DropDownGueltigkeitslisten_03

Abb. 3: So wird das Dropdown-Menü erzeugt.

5. Wählen Sie nun Zelle B3 aus. In dieser Zelle sollen die Städte entsprechend der Länderauswahl angezeigt werden.

6. Öffnen Sie auch hier den Dialog zur Gültigkeitsprüfung.

7. Wählen Sie im Feld Zulassen wiederum Liste und tragen Sie in das Feld Quelle folgenden Verweis ein: =INDIREKT(A3)

8. Beenden Sie auch diese Dialogbox mit einem Klick auf die Schaltfläche OK.

9. Nun ist es geschafft: Es wird jetzt nur noch im Drop-down-Menü Land beispielsweise Italien ausgewählt und automatisch beim Aktivieren des zweiten Drop-down-Menüs dynamisch die Städte angezeigt, in denen sich die jeweiligen Landesniederlassungen befinden.

DropDownGueltigkeitslisten_04

Abb. 4: Die beiden Dropdown-Menüs sind jetzt miteinander verknüpft.

Hinweis:
Sie können das Ganze mit einem bedingten Format toppen und die ausgewählten Koordinaten hervorheben. Markieren Sie hierfür den Zellenbereich A6:E10 und rufen Sie über das Menü Start  ->  Formatvorlagen  ->  Bedingte Formatierung den Befehl Neue Regel auf (Excel 2003: Format  ->  Bedingte Formatierung).

Wählen Sie nun aus der Liste Regeltyp auswählen den Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden aus und tragen Sie anschließend in das Feld Werte formatieren, für die diese Formel wahr ist die Formel =B7=$B$3 ein. Vergeben Sie anschließend über die Schaltfläche Formatieren die gewünschte Formatierung und verlassen Sie anschließend den Dialog über OK.

DropDownGueltigkeitslisten_05

Abb. 5: So kann die Auswahl hervorgehoben werden.

Hinweise zu den verwendeten Funktionen

Die Funktion INDIREKT() gibt den Bezug auf einen Text-Wert zurück. Bezüge werden sofort ausgewertet, sodass die zu ihnen gehörenden Werte angezeigt werden. Die Funktion INDIREKT() wird verwendet, um den Bezug auf eine in einer Formel befindliche Zelle zu ändern, ohne die Formel selbst zu ändern.

Die Syntax für die Funktion INDIREKT() lautet INDIREKT(Bezug;A1), wobei Folgendes zu beachten ist: Der Parameter Bezug bezeichnet einen Bezug auf eine Zelle, die entweder einen Bezug in der A1-Schreibweise, einen Bezug in der Z1S1-Schreibweise, einen definierten Namen als Bezug oder einen Zellbezug als Zeichenfolge enthält. Wenn der Parameter Bezug einen unzulässigen Zellbezug angibt, so liefert die Funktion INDIREKT() den Fehlerwert #BEZUG! zurück. Der Parameter A1 ist ein Wahrheitswert, der angibt, welche Art von Bezug in der Zelle enthalten ist.

 

Demodatei:

Kommentare (30)

Diesen Beitrag kommentieren
30.6.2015, 14:45 Uhr
von dinni0
Wie löse ich das Problem bezüglich der nicht möglichen Vergabe von Zahlenwerten als Namesdefinition. Ich möchte keinen Unterstrich voranstellen, weil es sich in meinem Fall um Jahreszahlen handelt und diese schon in vielen meiner Formeln benutzt werden.
Antwort schreiben
15.6.2015, 12:59 Uhr
von Gary
Hallo. Ich finde die Anleitung auch sehr gut. Vielen Dank. Meine Frage: ich habe eine Liste mit insgesamt 1000 Zeilen in der in jeder Zeile jeweils zwei Dropdowns sich befinden. Wie kann ich in VBA einfach programmieren, dass sich der 2. Dropdown bei Änderung des ersten Dropdowns leeren soll? Danke für Eure Hilfe.
Antwort schreiben
von Gary
Keine Idee?
von Gary
Ich beziehe mich hierbei auf Ihre Antwort vom 19.8.2013, 15:52 Uhr
12.3.2015, 9:38 Uhr
von AG
Super Artikel, vielen Dank für diese genaue und einfache Anleitung!
Antwort schreiben

Unsere Produktempfehlung

  • 09017-0500_Lexware_Lexware_financial_office
    Lexware financial office
    Die zeitsparende Komplettlösung fürs Büro: Lexware financial office führt Ihre gesamten Buchungen durch, schreibt Rechnungen, bearbeitet Aufträge, rechnet Löhne und Gehälter ab und koordiniert Termine. Alles drin für die Vorbereitung der E-Bilanz!