Dynamisch benannter Bereich mit INDEX

Mit der INDEX-Funktion lassen sich dynamische Bereiche erstellen, die auch für benannte Bereiche bzw. Namen verwendet werden können.

=Startzelle:LetzteZelle
=Startzelle:INDEX(Spalte;Positionsangabe)
=$A$2:INDEX($A:$A;ANZAHL2($A:$A))

1) Wir benötigen einen Zellbereich für den benannten Bereich. Dieser hat die fixe Startzelle $A$2 und eine dynamisch letzte Zelle.

2) Diese letzte Zelle können wir uns mit der INDEX-Funktion holen, denn diese Funktion gibt uns einen Verweis auf die Zelle als Ergebnis zurück.

3) Wie müssen der INDEX-Funktion sagen, für welche Zelle wir den Bezug haben wollen. Dazu wird in Spalte A geschaut. Die Positionsangabe, also in welcher Zeile sich die letzte Zelle befindet, ermitteln wir mit ANZAHL2. Diese Funktion liefert uns die Anzahl nicht leerer Zellen, also Zellen die Einträge haben. In unserem Fall 9. INDEX liefert uns nun den Bezug der 9. Zelle in der Spalte A, also A9.

4) Excel erkennt das Ergebnis der INDEX-Funktion als Zellbezug und kann daher dynamisch den Bereich $A$2:A9 zusammensetzen. Hier muss nicht noch extra mit INDIREKT gearbeitet werden.

5) Die Formel können wir nun im Namensmanager als neuen Namen eintragen (rngINDEX).

• Wenn der dynamische Bereich mehrere Spalten breit sein soll, können wir die Formel entsprechend erweitern: =$A$2:INDEX($A:$ZZ;ANZAHL2($A:$A);ANZAHL2(1:1))