Abhängige (dynamische) Drop-Down-Listen

In Formularen sind abhängige Drop-Down-Listen oftmals unabdingbar. Was bedeutet das? Wählt man im ersten Feld eine Oberkategorie "Obst" oder "Fleischwaren", dann sollen in einer zweiten Auswahlliste nur die entsprechenden Einträge zur Option stehen. Wir haben diese abhängigen Drop-Down-Listen bereits im Beitrag "Dropdown einer Dropdown-Liste erstellen - abhängige Dropdown" illustriert. Das funktioniert dann einwandfrei, wenn sich die Inhalte der Unterkategorien nicht ändern. Ansonsten ist es notwendig, die Bereiche (Namen) von "Obst" oder "Fleischwaren" den neuen Umständen entsprechend anzupassen (Bereich erweitern oder reduzieren). Es suggeriert also eine dynamische Drop-Down-Liste. Wie man einen solchen Namen (mit der Formel BEREICH.VERSCHIEBEN) anlegt, kannst Du in diesem Beitrag nachschlagen: Bereich eines Namens automatisch erweitern

Wir würden also alle Namen für die Drop-Down-Listen entsprechend anlegen, wie im Screenshot unten dargestellt (mit der Formel "BEREICH.VERSCHIEBEN", referenzierend auf das separate Blatt "Parameter"):

Namenslisten-Dynamisch

Wählt man im Anschluss im Formular die erste Option zu "Essen", sollte in der abhängigen Drop-Down-Liste dann die jeweilige Unterkategorie abrufbar sein. Dies ist jedoch nicht der Fall, die Auswahl-Liste lässt sich mit Klick auf den Erweiterungspfeil gar nicht erst aufrufen und anzeigen. Wie können wir also sowohl abhängige wie auch dynamische Drop-Down-Listen erstellen? Das Vorgehen zeige ich Dir nachfolgend Schritt für Schritt auf, lösche jedoch bitte im Namensmanager (Menüpunkt "Formeln" → "Namensmanager") allfällige bereits definierten Namen für Deine Drop-Down-Listen.

Abhängige, dynamische Drop-Down-Listen (Lösungsansatz)

Wir müssen die ursprünglichen Daten auf dem separaten Blatt "Parameter" also anders definieren, um jederzeit unsere Auswahl-Kriterien ergänzen und unmittelbar nachher im Formular abrufen können. Gehe dazu im Menüband auf das Register "Einfügen" und selektiere die Option "Tabelle". Du erstellst nun also eine Tabelle und wählst als erstes den Bereich "A1:A3" aus, um die Oberkategorie abzubilden. Falls Du die Zeile 1 inkludierst, wähle zudem "Tabelle hat Überschriften".

TabelleEinfuegenUndDefinieren

Wiederhole den Vorgang auch für die Unterkategorien "Fleischwaren" und "Obst".

Wenn Du nun in der Spalte A Fleischwaren und Obst markierst, siehst Du links oben im Namensfeld (normalerweise steht da die Zelle "A2" und so weiter) "Tabelle1" stehen. Der Name "Tabelle1" ist aktuell also stellvertretend für diesen Bereich. Klicke in dieses Namensfeld und überschreibe einfach "Tabelle1" mit "Essen" - bestätige mit der Enter-Taste. Du wirst in dem Namensfeld kurz etwas aufblinken sehen, danach steht allerdings wieder "Tabelle1" drin. Mache das nun auch für B2:B3 (=Tabelle2) und setze da den Namen "Fleischwaren" sowie für C2:C4 (=Tabelle3) und definiere da "Obst". Rufe nun erneut Deinen Namensmanager auf (Menüpunkt "Formeln" → "Namensmanager"). Dort siehst Du die Resultate entsprechend - beachte auch die Spalte "Bezieht sich auf", die auf die jeweilige Tabelle referenziert:

NamensmanagerTabellenReferenzen

Navigiere nun auf Dein effektives Formular. Wähle die Hauptkategorie (Essen) und versuche nun, Deine Unterkategorie (Drop-Down-Einstellung: =INDIREKT(B5) -- wobei B5 dem Feld mit Fleischwaren/Obst entspricht) kann nun korrekt aufgerufen werden:

FormularDynamischeDropDownUnterkategorie

Zuletzt kannst Du prüfen, ob Deine Listen auf dem Blatt "Parameter" tatsächlich dynamisch sind. Gib einfach einen neuen Begriff unterhalb ein, beispielsweise "Schweinefleisch" als zusätzliche Fleischware. Die Tabelle erweitert sich automatisch (Du siehst dies auch farblich) - und auf der Drop-Down-Liste im Formular kannst Du die neue Option ebenfalls gleich selektieren.

Pivot-Datenquelle automatisch erweitern

Pivot-Tabellen aggregieren innert kürzester Zeit immense Datenmengen. Sobald man der Datenquelle weitere Informationen anfügt, wird dies standardmässig nicht direkt erkannt - der neue Bereich ist jeweils manuell anzupassen:

pivotdatenquelleaendern

Es besteht jedoch die Möglichkeit, die Datenquelle automatisch erweitern zu lassen. Dies funktioniert im Grunde genommen, wie wir das bereits in diesem Beitrag hier mittels Namensgebung für Bereiche behandelt haben. Unserer Pivot-Datenquelle teilen wir auch hier zuerst einen Namen zu: Markiere die gesamte Datenquelle und schreibe ins Namensfeld (links neben der Bearbeitungsleiste) Deinen gewünschten Namen, bspw. "Datenbasis":

pivotdatenquellenamensfeld

Gehst Du nun via "Einfügen" → "PivotTable" kannst Du direkt bei "Tabelle/Bereich:" den definierten Namen ("Datenbasis") eingeben:

(Beachte: Unsere Pivot wird in einem neuen Tabellenblatt sein, nicht im selben wie die Datenbasis.)

Nun ist aber noch nicht konfiguriert, dass sich der Bereich automatisch erweitern soll, sobald weitere Daten angefügt werden (weitere Zeilen, weitere Spalten). Hier kommt die Formel "BEREICH.VERSCHIEBEN" zum Einsatz. Gehe via "Formeln" auf "Namensmanager" und bearbeite Deinen bereits angelegten Namen. In der Maske gibst Du bei "Bezieht sich auf:" die nachfolgende Formel ein:

=BEREICH.VERSCHIEBEN(Datenbasis!$A$1;;;ANZAHL2(Datenbasis!$A:$A);ANZAHL2(Datenbasis!$1:$1))

Auf dem Tabellenblatt mit ebenfalls dem Namen "Datenbasis", beginnend bei Zelle "A1" wird einerseits nach unten (Spalte A:A) gezählt, wie viele Einträge vorhanden sind. Dasselbe passiert auf der Zeile 1.

pivotdatenquellenamensmanager

Der Bereich wird entsprechend daraufhin erweitert, sobald neue Einträge angefügt werden. (Beachte, dass die Pivot-Tabelle noch via Rechtsklick → "Aktualisieren" aufzufrischen ist.)

Summe nach Zellfarbe bilden

Eine SUMMEWENN-Funktion berücksichtigt zwar Kriterien und bildet bei Erfüllen dieser die Summe der jeweiligen Werte, beispielsweise die Zellfarbe kann allerdings nicht ohne weiteres als Kriterium erkannt und berücksichtigt werden. Dazu benötigt es einen zusätzlichen Schritt, welchen wir in diesem Beitrag betrachten:

Im Menüband gehst Du unter "Formeln" auf "Name definieren". Gib dort als Name "Farbe" ein - dies ist später der Name einer Formel, welche wir in eine Hilfsspalte eingeben werden (Du kannst daraufhin "=Farbe" in eine Zelle eingeben). Bei "Bezieht sich auf" gibst Du folgendes ein:

=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))

Zusammengefasst sieht Dein definierter Name wie folgt aus:

ZellfarbeName

[Der Bezug ZS(-1) ist ein Referenzbezug: Dieser bedeutet, dass Du auf die selbe Zeile (Z), allerdings auf eine Spalte (S) nach links (-1) zugreifst.]

Wenn Du nun eine Spalte rechts Deiner farbigen Zelle "=Farbe" eingibst, wirst Du eine Zahl erhalten - diese bezieht sich auf die Zellfarbe. Wenn Du die Zellfarbe änderst, wird sich auch das Resultat in der Zelle nebenan verändern, sobald Du die "=Farbe"-Formel aktualisierst (oder F9 auf der Tastatur zur Kalkulation des gesamten Tabellenblatts drückst). Nun kannst Du die bekannte SUMMEWENN-Formel verwenden und alle Werte summieren, welche in der Spalte nebenan den entsprechenden Farbcode aufweisen. In diesem Beispiel erstelle ich die Summe aller orangen Zellen (mit dem Farbcode 44):

SummewennZellfarbe

(10 + 2 + 3 + 5 + 10 = 30)

Wichtig zu beachten ist, dass Du Deine Datei daraufhin als .xlsm (Makro-Datei) abspeichern musst, damit die "=Farbe"-Funktion auch zu einem späteren Zeitpunkt wieder funktionsfähig ist.