Dynamisch benannter Bereich mit INDIREKT

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

=INDIREKT("Startzelle:LetzteZelle")
=INDIREKT("Startzelle:Spaltenbuchstabe"&Zeilennummer)
=INDIREKT("$A$2: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) Mit Hilfe der INDIREKT Funktion können wir aus einem ganz einfachen Text bzw. Textstring einen Bezug bauen. 

3) Den ersten Teil des Textes kennen wir bereits "$A$2:A". Uns fehlt lediglich die Zeilennummer, der letzten Zelle in unserem Wertebereich. Die Zeilennummer, 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.

4) Diese Zeilennummer wird nun mit dem & Zeichen mit dem Rest des Texten verkettet.
"$A$2:A"&9 = "$A$2:A9"
 Nun kann INDIREKT den Text "$A$2:A9" in einen Bezug umwandeln.

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

• Wenn der dynamische Bereich mehrere Spalten breit sein soll, müssen wir den Spaltenbuchstaben auch dynamisieren und die Anzahl der Spalten bzw. die Spaltennummer in den Spaltenbuchstaben umwandeln: =INDIREKT("$A$2:"&WECHSELN(ADRESSE(1;ANZAHL2($1:$1);4);"1";"")&ANZAHL2($A:$A))