Bereichsnamen werden typischerweise verwendet, um einem definierten Zellbereich z. B. A1:B20 einen Namen zuzuweisen, über den dieser Bereich angesprochen werden kann. Der Befehl zur Erstellung von Bereichsnamen wird über das Menü Formeln / Definierte Namen / Namen definieren / Namen definieren aufgerufen. In diesem Beitrag möchten wir Ihnen eine Möglichkeit zeigen, wie einem bestimmten Bereichsnamen dynamisch unterschiedliche Zellen zugewiesen werden können.
Im Beispiel liegt eine Mitarbeitertabelle getrennt nach Niederlassungen vor, siehe Abbildung 1.
Abbildung 1
In Zelle C4 befindet sich ein DropDown-Menü (Datengültigkeit), über das zwischen den vier Niederlassungen gewählt werden kann, siehe Abbildung 2.
Abbildung 2
Ziel soll nun sein, dass nach Auswahl einer Niederlassung die entsprechenden Mitarbeiter in dem DropDown-Auswahlfeld in Zelle C5 zur Verfügung stehen und ausgewählt werden können. So sollen beispielsweise bei Auswahl der Niederlassung Köln die Mitarbeiter Monsens, Niesel, Persich und Reiser als Auswahl zur Verfügung stehen.
Dazu wird ein Bereichsname mit der Bezeichnung Mitarbeiter definiert. Als Zellbezug muss folgende Formel verwendet werden, siehe auch Abbildung 3.
=WENN('Dynamische Bereichsnamen'!C4="München";'Dynamische Bereichsnamen'!F5:F8;WENN('Dynamische Bereichsnamen'!C4="Hamburg";'Dynamische Bereichsnamen'!G5:G8;WENN('Dynamische Bereichsnamen'!C4="Köln";'Dynamische Bereichsnamen'!H5:H8;WENN('Dynamische Bereichsnamen'!C4="Berlin";'Dynamische Bereichsnamen'!I5:I8;""))))
Abbildung 3
Diese Formel prüft, welche Niederlassung in Zelle C4 ausgewählt wurde und weist dann dem Bereichsnamen Mitarbeiter den entsprechenden Zellbereich zu. Bei München wird der Zellbereich F5:F8 zugewiesen, bei Auswahl von Hamburg entsprechend der Zellbereich H5:H8 und so weiter.
Sobald nun eine andere Niederlassung in Zelle C4 ausgewählt wird, wird die Auswahlliste in Zelle C5 dynamisch aktualisiert.
Über den folgenden Link können Sie die Beispieldatei herunterladen.
Eine weitere Möglichkeit der Datenauswahl kann über die Funktion SVERWEIS() durchgeführt werden. Damit entfallen umfangreiche WENN-Verschachtelungen.
Erfassen Sie dazu anstatt der WENN-Formel folgende Formel beim Bereichsnamen "Mitarbeiter".
=INDIREKT(WVERWEIS('Dynamische Bereichsnamen'!$C$4;'Dynamische Bereichsnamen'!$F$4:$I$9;2;0))
Diese Formel liefert das gleiche Ergebnis, ist jedoch für umfangreichere Datentabellen leichter anzupassen. Die Lösung können sie über den folgenden Link herunterladen.