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 (26)

Diesen Beitrag kommentieren
5.6.2014, 13:37 Uhr
von Kay
Es funktioniert bei mir (fast). Aber immer beim letzten schritt, bei dem ich INDIREKT(F3) (F3 ist das Feld mit der ersten Dropdown Liste) kommt beim Klicken auf OK das Fenster "Die Quelle untersucht gerade einen möglichen Fehler. Möchten Sie den Vorgang fortsetzen?" ich klicke dann auf OK aber ich kann dann im zweiten DropDown menü nichts auswählen und eingeben was ich will.
Antwort schreiben
von tz
Sonderzeichen sind auch verboten und der Name darf nicht mit einer Ziffer anfangen. Das hat bei mir geholfen.
von Kay
@RC: Hab ich gemacht, Excel findet mit Suchfunktion auch kein Leerzeichen mehr, ich hab alles durch Unterstriche ersetzt, Leere Zellen auch. Gleiche Einträge dürfen aber schon vorkommen oder? Ich hab nach dem Entfernen der Leerzeichen auch die Gültigkeiten nochmal neu zugeordnet aber das hat auch nichts gebracht, ich krieg am Ende immer noch die gleiche "Fehlermeldung"
von RC
@Kay: Check mal die Felder, die von den =INDIREKT Formeln genutzt werden. Dort darf nirgends ein Leerzeichen/Sonderzeichen vorhanden sein. Den gleichen Fehler hatte ich auch und es war damit behoben.
20.5.2014, 14:39 Uhr
von Chante
Great post however I was wanting to know if you could write a litte more on this topic? I'd be very thankful if you could elaborate a little bit further. Cheers!
Antwort schreiben
17.5.2014, 7:09 Uhr
von Pharmg159
Very nice site!
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!