Wir haben uns bereits den Namensmanager und die Dropdown-Listen angeschaut. Wenn Du zu der Kriterienliste weitere Begriffe hinzufügst, wird der Bereich Deines Namens allerdings nicht automatisch erweitert - Du musst via Namensmanager die letzte Zelle manuell angeben, respektive den effektiven Bereich neu selektieren. Mit der Formel "BEREICH.VERSCHIEBEN" kann dieser Schritt automatisiert werden. Excel sagt uns folgendes über diese Formel:
Gibt einen Bezug zurück, der gegenüber dem angegebenen Bezug versetzt ist.
Man kann damit nicht nur den Bereich "A1:A3" nach "A3:A5" verschieben, sondern auch auf "A1:A5" erweitern - und genau das wollen wir in diesem Beitrag thematisieren. Schauen wir uns einmal die Syntax der Formel an:
BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])
Wir beginnen also mit einem Bezug - einer einzelnen Zelle (A1). "Zeilen" und "Spalten" stehen jeweils für eine Verschiebung um X Zellen nach unten respektive nach rechts. "[Höhe]" und "[Breite]" hingegen lässt uns einen Bereich nach unten oder nach rechts erweitern. Mithilfe der Formel "ANZAHL2" als "Höhe" kannst Du zählen, wie viele Argumente in einer Spalte stehen und so die Anzahl Zellen Deines Bereiches ermitteln. Die Formel lautet demnach:
=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2(A:A);)
Wir setzen A1 absolut ($), weil diese Formel als Namen definiert werden soll. Der Name ist in einem Tabellenblatt oder einer gesamten Excel-Arbeitsmappe vorhanden. Ohne die Dollarzeichen würde sich deshalb in anderen Zellen die Referenz automatisch verschoben werden und nicht an A1 festhalten. Beachte anschliessend die nacheinanderfolgenden Semikolon (;) - wir geben weder bei "Zeilen" noch bei "Spalten" etwas ein - die ANZAHL2-Formel ermittelt die Höhe unseres Bereiches anhand sämtlicher nichtleerer Zellen in Spalte A.
Die obengenannte Formel musst Du bei Deinem Namen als Bezug eingeben, um die Kriterien nachher in Deiner Dropdown-Liste aufzuführen (siehe "Namensmanager" und "Dropdown-Liste"). Klicke hierfür auf den Menüpunkt "Formeln" → "Namen definieren". Der Bezug "$A$1" wird Dir automatisch mit dem Namen des entsprechenden Tabellenblattes ergänzt (bspw. Tabelle1!$A$1). Bitte beachte deshalb folgendes, wenn Du auf andere Tabellenblätter Bezug nehmen musst: Falls Du Deine Kriterien zum Beispiel im separaten Tabellenblatt "Parameter" hast, heissen Deine Bezüge "Parameter!$A$1" und "Parameter!A:A".
Wenn wir nun ein neues Kriterium zu unserer Liste hinzufügen, wird dies automatisch zum Namen und folglich in die Dropdown-Liste mitaufgenommen.