Generische Formel
=GET.WORKBOOK(1)&T(NOW())
Zusammenfassung
Um Arbeitsblätter in einer Excel-Arbeitsmappe aufzulisten, können Sie einen zweistufigen Ansatz verwenden: (1) Definieren eines benannten Bereichs mit dem Namen "Blattnamen" mit einem alten Makrobefehl und (2) Verwenden der INDEX-Funktion zum Abrufen von Blattnamen mit dem benannten Bereich. In dem gezeigten Beispiel lautet die Formel in B5:
=INDEX(MID(sheetnames,FIND(")",sheetnames)+1,255),ROWS($B$5:B5))
Hinweis: Ich habe diese Formel im MrExcel-Forum in einem Beitrag von T. Valko gefunden.
Erläuterung
Der benannte Bereich "Blattnamen" wird mit folgendem Code erstellt:
=GET.WORKBOOK(1)&T(NOW())
GET.WORKBOOK ist ein Makrobefehl, der ein Array von Blattnamen in der aktuellen Arbeitsmappe abruft. Das resultierende Array sieht folgendermaßen aus:
("(workbook.xlsm)Sheet1","(workbook.xlsm)Sheet2","(workbook.xlsm)Sheet3","(workbook.xlsm)Sheet4","(workbook.xlsm)Sheet5")
Ein kryptischer Ausdruck wird mit dem Ergebnis verknüpft:
&T(NOW())
Der Zweck dieses Codes besteht darin, eine Neuberechnung zu erzwingen, um Änderungen an Blattnamen zu erfassen. Da NOW eine flüchtige Funktion ist, wird sie bei jeder Arbeitsblattänderung neu berechnet. Die NOW-Funktion gibt einen numerischen Wert zurück, der Datum und Uhrzeit darstellt. Die T-Funktion gibt eine leere Zeichenfolge ("") für numerische Werte zurück, sodass die Verkettung keine Auswirkungen auf die Werte hat.
Zurück im Arbeitsblatt enthält Zelle B6 die folgende Formel:
=INDEX(MID(sheetnames,FIND(")",sheetnames)+1,255),ROWS($B$5:B5))
Mit der MID-Funktion werden die Arbeitsblattnamen von innen nach außen entfernt. Das resultierende Array sieht folgendermaßen aus:
("Sheet1","Sheet2","Sheet3","Sheet4","Sheet5")
Dies geht in die INDEX-Funktion als "Array". Die ROW-Funktion verwendet einen erweiterten Bereich, um eine inkrementelle Zeilennummer zu generieren. In jeder neuen Zeile gibt INDEX den nächsten Array-Wert zurück. Wenn keine Blattnamen mehr ausgegeben werden müssen, gibt die Formel einen # REF-Fehler zurück.
Hinweis: Da diese Formel auf einem Makrobefehl basiert, müssen Sie sie als makrofähige Arbeitsmappe speichern, wenn die Formel die Blattnamen nach dem Schließen und erneuten Öffnen der Datei weiterhin aktualisieren soll. Wenn Sie als normales Arbeitsblatt speichern, wird der Blattnamencode entfernt.
