Einrichtung von Verweisen in Nachschlagespalten auf eine dynamisch erweiterbare Liste

1. Grundidee und Anwendungsszenario
2. Voraussetzungen und Begriffe
3. Methoden zur dynamischen Bereichserstellung
4. Konkrete Schritt-für-Schritt-Anleitung für Excel mit Excel-Tabelle
5. Alternativ: Schritt-für-Schritt mit dynamischem benannten Bereich
6. Wichtige Hinweise

Grundidee und Anwendungsszenario

Wenn Sie in Excel oder ähnlichen Tabellenprogrammen eine Nachschlagespalte (Dropdown-Liste) erstellen wollen, die eine Liste von Einträgen als Auswahl anzeigt, sollte sich diese Liste dynamisch erweitern lassen. Das bedeutet, wenn Sie weitere Elemente zu Ihrer Liste hinzufügen, sollen diese automatisch im Dropdown-Menü erscheinen. Dieses Vorgehen ist besonders nützlich bei Formulareingaben, Datenbanken, Inventarlisten oder in jeder Anwendung, wo eine Nutzerauswahl auf Basis ständig wachsender Listen stattfinden soll.

Die Herausforderung besteht darin, den Bereich der Nachschlagespalte so zu definieren, dass er automatisch auf alle aktuellen Einträge der Liste zugreift – ohne, dass Sie manuell die Bereichsdefinition ändern müssen. Zur Lösung werden dynamische Bereiche oder Tabellen verwendet.

Voraussetzungen und Begriffe

Zuerst benötigen Sie eine Liste mit Werten, die als Quelle für das Dropdown-Menü dienen sollen. Diese Liste steht z.B. in einem Bereich wie A2:A100 oder in einer Spalte Ihrer Tabelle.

Die Nachschlagespalten befinden sich an einer anderen Stelle im Tabellenblatt oder sogar in einem separaten Blatt, und Sie wollen dort den Benutzer zur Auswahl eines Eintrags aus der Liste zwingen.

Methoden zur dynamischen Bereichserstellung

Ein statischer Bereich wird in der Gültigkeitsregel festgelegt, z.B. =A2:A20. Fügt man später Zeilen hinzu, muss man den Bereich manuell anpassen.

1. Verwendung der Excel-Tabelle (List Object): Wenn Sie Ihre Liste als offizielle Excel-Tabelle formatieren (Markieren Sie die Liste und drücken Sie STRG+T), dann erweitert sich der Tabellenbereich automatisch, wenn Sie neue Daten hinzufügen. Nennen Sie der Tabelle z.B. Tabelle1 und die Spalte mit den Einträgen z.B. Name. Ihre Nachschlagequelle lautet dann =Tabelle1 . Im Datenüberprüfungsdialog geben Sie diese Formel als Quelle ein und erhalten eine Dropdown-Liste, die automatisch mitwächst.

2. Dynamischer benannter Bereich mit dynamischer Formel (z.B. mit BEREICH.VERSCHIEBEN oder INDIREKT): Wenn Sie keine Tabelle verwenden möchten, können Sie einen dynamischen Bereich definieren: Über die Registerkarte Formeln > Namensmanager definieren Sie einen Namen wie ListeDynamisch mit folgender Formel:

=BEREICH.VERSCHIEBEN($A$2;0;0;ANZAHL2($A$2:$A$100);1)

Diese Formel erzeugt einen Bereich, der in Spalte A bei A2 beginnt und so viele Zeilen einbezieht, wie in A2:A100 Werte (nicht leer) stehen. Wenn Sie nun Werte unten anfügen, wächst der Bereich automatisch.

Konkrete Schritt-für-Schritt-Anleitung für Excel mit Excel-Tabelle

Öffnen Sie Ihre Arbeitsmappe und gehen Sie zu der Liste, die Sie als Nachschlagequelle verwenden möchten. Markieren Sie den Bereich mit Ihren Werten inklusive Überschrift oben.

Drücken Sie STRG + T, um eine Tabelle zu erstellen und bestätigen Sie den Bereich. Excel erstellt eine formatierte Tabelle mit einem eigenen Namen, z.B. Tabelle1. Sie können diesen Namen unter Tabellentools > Entwurf > Tabellenname anpassen.

Vermerken Sie den Namen der Spalte, etwa Name. In der Zelle, in der Sie ein Dropdown-Menü wünschen, wählen Sie Daten > Datenüberprüfung > Datenüberprüfung.... Wählen Sie bei Zulassen den Punkt Liste.

=Tabelle1

Bestätigen Sie mit OK. Nun wird im Dropdown automatisch die gesamte Spalte der Tabelle als Auswahl angezeigt, auch wenn Sie neue Einträge unten hinzufügen.

Alternativ: Schritt-für-Schritt mit dynamischem benannten Bereich

Markieren Sie den Bereich, in dem Sie Ihre Liste pflegen, z.B. von A2 bis A100 (oder mehr). Gehen Sie auf Formeln > Namensmanager > Neu.

Geben Sie als Namen z.B. ListeDynamisch ein. Unter Bezieht sich auf geben Sie folgende Formel ein:

=BEREICH.VERSCHIEBEN($A$2;0;0;ANZAHL2($A$2:$A$100);1)

Je nachdem, ob Ihre Liste leere Zellen enthalten kann oder nicht, können Sie auch andere Funktionen wie ZÄHLENWENN oder VERGLEICH verwenden, um die Länge des Bereichs flexibel zu bestimmen. Stellen Sie sicher, dass der Bereich unabhängig von zukünftigen Einträgen groß genug bemessen ist.

Anschließend wechseln Sie zur Zelle für das Dropdown-Menü, wählen Datenüberprüfung > Liste und tragen als Quelle ein:

=ListeDynamisch

Wichtige Hinweise

Der dynamische Bereich muss zwingend zusammenhängend sein, also keine leerzeilen in der Mitte der Liste enthalten. Wenn sich Leerzeilen in Ihrer Liste befinden, kann dies dazu führen, dass der Bereich zu früh endet oder falsche Werte enthält.

Bei Verwendung von Excel-Tabellen wird automatisch die Struktur gepflegt und die Nachschlagespalten funktionieren zuverlässiger, weil die Tabelle automatisch erweitert wird.

Wenn Sie in Google Sheets arbeiten, gibt es ähnliche Konzepte mit benannten Bereichen und dynamischen Arrayformeln.

Zusammenfassung: Verwenden Sie idealerweise Excel-Tabellen als Quelle für Nachschlagespalten. Diese passen sich automatisch an neue Listeneinträge an. Alternativ definieren Sie einen dynamischen benannten Bereich mit der Funktion BEREICH.VERSCHIEBEN und nutzen diesen als Quelle in der Datenüberprüfung.