Soprani Software

Sie können sich erinnern? Ich hab Ihnen neulich erzählt, wie praktisch es ist, wenn Sie Bereichen einen Namen geben. Was aber, wenn der Bereich selbst „wachsen“ kann – immer wieder werden unten Datensätze angefügt. Und das vielleicht auch noch von anderen Menschen. Müssen Sie diese dann darin einschulen, wie sie den Namen neu richtig setzen?

Natürlich nicht. Erzeugen Sie den Bereich für den Namen dynamisch.

Das geht so: Sie öffnen über FORMELN > definierte Namen > Namensmanager den Namensmanager und öffnen mit Neu… den Dialog für die Namens-Neuanlage.

Als Name suchen Sie sich etwas Passendes (nur Buchstaben, Ziffern und _, und ein Buchstabe zu Beginn, und kein reserviertes Wort) aus, ich hab mich für lst_Ort entschieden. Den Kommentar können Sie frei wählen, er dient nur dazu, dass Sie in sieben Jahren noch nachlesen können, wozu Sie diesen Namen erstellt haben. Und bei Bezieht sich auf kommt jetzt das Dynamische:

=BEREICH.VERSCHIEBEN(Name_dyn!$A$1;1;0;
ANZAHL2(Name_dyn!$A:$A)-1;1)

So. Fertig. Funktioniert.

Und was heißt das? Fitzeln wir das auseinander, und betrachten wir dazu die Funktion BEREICH.VERSCHIEBEN näher. Sie schaut so aus:
BEREICH.VERSCHIEBEN(Startzelle;nachunten;nachrechts;Höhe;Breite). Und liefert als Ergebnis den Bereich, der nachunten Zeilen und nachrechts Spalten von der Startzelle aus verschoben ist und Höhe Zeilen lang und Breite Zeilen breit ist. Wenn Höhe und Breite nicht angegeben sind, gibt’s nur eine Zelle zurück.

Und wir verwenden hier

• Startzelle: Name_dyn!$A$1 . Es geht also in der Zelle A1 des Blatts Name_dyn los mit unserer Bereichverschieberei. 
• nachunten: 1. Der Bereich mit den Orten beginnt also eine Zelle unterhalb der Startzelle. In Zeile 2. Passt.
• nachrechts: 0. Der Bereich mit den Orten beginnt in derselben Spalte wie die Startzelle – in Spalte A also.
• –> Der Bereich mit den Orten beginnt in A2. Perfekt.
• Höhe: ANZAHL2(Name_dyn!$A:$A)-1. Da ist jetzt der eigentliche Trick versteckt. Wir zählen die nicht-leeren Zellen in der Spalte A auf dem Blatt Name_dyn mit der Funktion ANZAHL2 und ziehen davon 1 ab (die Überschrift). In dem Beispiel kommt bei ANZAHL2(Name_dyn!$A:$A) 9 heraus, davon ziehen wir wie Hans Rosenthal eins ab, macht acht Orte. Hurra!
• Breite: 1. Wir haben nur eine Spalte, die den Orte-Bereich ausmacht.

Und damit verweist lst_Orte auf den Bereich Name_dyn!A2:A9. Und wenn wir unten Zwettl dazuschreiben, verweist lst_Orte auf Name_dyn!A2:A10. Und wenn …

Das gefällt Ihnen, oder?

Falls Sie mit einer englischen Version arbeiten: BEREICH.VERSCHIEBEN heißt da elegant OFFSET. Und ANZAHL2 entspricht COUNTA.