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

Diesen Beitrag kommentieren
7.4.2014, 11:21 Uhr
von Yasin
Vielen Dank. Das ist eine super Anleitung! Eine Frage habe ich aber noch: Ich habe eine lange Liste, wo ich dann mehrere Listen habe. Die Level 1 Liste kann ich problemlos vervielfältigen. Bei der Level 2 Liste funktioniert es aber nicht. Können Sie mir diesbezüglich weiterhelfen? Danke im Voraus.
Antwort schreiben
von Lexware Online Redaktion
Hallo Yasin, was genau funktioniert denn bei der 2. Liste nicht?
26.3.2014, 10:59 Uhr
von Florian
Wow, super erklaert - vielen vielen Dank. Ich habe nur ein Problem. Mein erstes Level heisst "Arts and Crafts", dann findet es mir aber die zweile Auswahl ("Mosaic" und "MnZ" NICHT). Ersetzte ich nun die Leerzeichen bei "Arts and Crafts" mit einem Unterstrich, funktioniert es wunderbar. Aber ich habe eine Datei von einem Kunden geshen, dort wird ebenfalls mit Leerzeichen gearbeitet. Was muss ich denn aendern, dass die Leerzeichen nicht zum Problem fuehren?
Antwort schreiben
von Lexware Online Redaktion
Hallo Florian, schicke doch am besten deine Beispielmappe an excel@lexware.de. Wir schauen es uns dann an.
von Florian
Ne, leider nicht - es kommt zwar keine Fehlermeldung mehr, aber im zweiten Drop Down Menu ist kein Eintrag mehr zu sehen. Mach ich etwas falsch?
von Lexware Online Redaktion
Hallo Florian, ersetze in der Zelle wo "Frankreich Europa" steht, den Text durch die Formel = VERKETTEN("Frankreich" & " " & "Europa") Löst das dein Problem?
von Florian
Danke fuer die Antwort! Wo meinst du, soll ich die Formel einfuegen? Vielleicht habe ich mich auch nicht ganz klar ausgedruckt. Was ich meine, ist folgendes: Wenn du in deinem Beispiel oben anstatt Frankreich z.B. Frankreich Europa, Italien Eurpa, Spanien Europa, ... als Felder hast. Bei mir kommt durch die Leerzeichen zwischen Land und Europa eine Fehlermeldung. Kann man das umgehen?
von Lexware Online Redaktion
Hallo Florian, probiere doch mal die Funktion VERKETTEN aus. Schreibe einfach in die gewünschte Zelle diese Formel statt dem Text : = VERKETTEN("Test1" & " " & "Test2") Hilft dir das weiter?
26.3.2014, 10:58 Uhr
von Florian
Wow, super erklaert - vielen vielen Dank. Ich habe nur ein Problem. Mein erstes Level heisst "Arts and Crafts", dann findet es mir aber die zweile Auswahl ("Mosaic" und "MnZ" NICHT). Ersetzte ich nun die Leerzeichen bei "Arts and Crafts" mit einem Unterstrich, funktioniert es wunderbar. Aber ich habe eine Datei von einem Kunden geshen, dort wird ebenfalls mit Leerzeichen gearbeitet. Was muss ich denn aendern, dass die Leerzeichen nicht zum Problem fuehren?
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!