Eine auf den ersten Blick triviale Fragestellung entpuppt sich als ziemlich kniffelig:
Wie kann man eingentlich in einer Excel-Formel den Namen eines Arbeitsblattes ermitteln?
Wozu man so etwas braucht? Zum Beispiel, um ein Inhaltsverzeichnis seiner Arbeitsblätter zu erstellen, welches sich automatisch anpasst, wenn ein Blatt umbenannt wird.
Kaum zu glauben, aber es gibt in Excel dafür keine Standardfunktion. Trotzdem ist eine Lösung ohne großen Aufwand möglich.
Und so geht’s:
Excel bietet eine Funktion, mit der sich Informationen zur Formatierung, zur Position oder zum Inhalt einer angegebenen Zelle auslesen lassen. Sinnigerweise heißt diese Funktion ZELLE().
=ZELLE(Infotyp;[Bezug])
Über den Parameter „Infotyp“ können folgende Informationen abgefragt werden:
- „Adresse“
- „Spalte“
- „Farbe“
- „Inhalt“
- „Dateiname“
- „Format“
- „Klammern“
- „Präfix“
- „Schutz“
- „Zeile“
- „Typ“
- „Breite“
Das ist schon eine ganze Menge, aber der Name des Arbeitsblattes ist leider nicht dabei. Oder etwa doch?
Der Trick mit dem Dateinamen
Wie man der oben genannten Liste entnehmen kann, lässt sich zumindest der Dateiname ermitteln. Und genau hier setzen wir an. Dazu öffnen wir eine neue Exceldatei und speichern diese unter einem beliebigen Namen ab. Dieser Schritt ist sehr wichtig, da nur eine bereits gespeicherte Tabelle auch einen Dateinamen hat, den man ermitteln könnte.
Dann platzieren wir den Cursor irgendwo auf dem Tabellenblatt, z.B. in Zelle A1 und geben dort die Funktion ZELLE in ihrer einfachsten Form ein:
=ZELLE("dateiname")
Die Funktion ZELLE()
Wie man sieht, bekommt man dadurch den Namen der Excel-Datei inklusive komplettem Pfad angezeigt. Und: ganz am Ende steht auch der Name des Arbeitsblattes, hier „Tabelle1“, auf den es uns eigentlich ankommt.
Textwerkzeuge müssen her
Bei genauer Betrachtung erkennt man, dass der Arbeitsblattname nach der geschlossenen eckigen Klammer „]“ kommt. Wir bräuchten als nur den rechten Teil des mit ZELLE() ermittelten Pfades, beginnend genau nach der eckigen Klammer.
Ein Fall für das Duo FINDEN() und RECHTS().
Über die FINDEN()-Funktion ermitteln wir die Position der geschlossenen eckigen Klammer:
=FINDEN("]";A1)
Position der eckigen Klammer ermitteln
In meinem Beispiel befindet sich diese an der 25. Stelle. Nun brauche ich nur noch alle Zeichen rechts von dieser Position mit der RECHTS()-Funktion abzutrennen. Da wir allerdings nicht wissen, wie lange der Name des Arbeitsblatts ist, lassen wir das von Excel ausrechnen:
=LÄNGE(A1)-A2
Länge des Blattnamens ermitteln
Wir nehmen also die Länge des kompletten Pfades, der in Zelle A1 steht und ziehen davon die Position der Klammer ab, die wir in Zelle A2 ermittelt haben. Als Ergebnis erhalten wir die Länge des Arbeitsblattnamens, in meine Beispiel also 8.
Und damit haben wir alles, was wir benötigen, um mit der RECHTS-Funktion den Blattnamen auszugeben:
=RECHTS(A1;A3)
Ergebnis: Der Blattname
In verkürzter Form sieht das Ganze dann so aus:
=RECHTS(ZELLE("dateiname";A1);LÄNGE(A1)-FINDEN("]";A1))
Die Einzelschritte zusammengefasst
Und wer sich jetzt noch den Zwischenschritt über die Hilfsformel in Zelle A1 sparen möchte, kann das natürlich auch tun:
Alles in einer Formel
Das Praktische an der Geschichte: Der ausgegebene Name passt sich automatisch an, wenn das Arbeitsblatt umbenannt wird:
Automatische Anpassung inklusive
Ich gebe zu:
Eine etwas vertrackte Lösung, aber letztendlich doch mit den normalen Excel-Bordmitteln und ohne VBA-Programmierung.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Die übersichtliche Schritt für Schritt Darstellung finde ich sehr hilfreich.
Ein Problem: Wenn ich die Formel zur Bestimmung des Blattnamens
(„Den Namen des Arbeitsblattes in Excel ermitteln“)
auf verschiedenen Blättern einsetze, werden alle so bestimmten Blattnamen auf den zuletzt bestimmten Blattnamen geändert. Drücke ich F9, wird der Name des aktuellen Blattes wieder richtig bestimmt, dafür sind die anderen wieder falsch.
Wo steckt der (mein?) Fehler?
Hallo Dietmar,
das ist in der Tat ein kleines Problem und nicht etwa dein Fehler, sondern meiner. Folgt man meiner Anleitung, wird auf allen Blättern immer nur der Blattname der letzten Formel übernommen, wie du sehr richtig erkannt hast.
Es gibt aber eine einfache Lösung:
Man ändert die Formel =ZELLE(„dateiname“) in =ZELLE(„dateiname“;$A$1).
Damit bezieht sich die Formel immer auf die Zelle A1 im aktiven Blatt und somit wird auch der korrekte Registername ausgegeben.
Danke nochmal für den Hinweis.
Schöne Grüße,
Martin
Habe die Formel getestet und es hat funktioniert, aber nun mein Problem, meine Mappe hat aber ca. 15 Blätter mit jeweils anderen Namen im Reiter. Wie bekomme ich die den angesprochen um später alle auf einer Seite zu haben. Naja, wahrscheinlich denke ich wieder mal in die falsche Richtung.
Viele Grüße Joe
Hallo Joe,
das ist kein Problem, du musst nur in der Formel zur Zelle A1 den jeweiligen Blattnamen im Bezug angeben, also z.B. so:
=RECHTS(ZELLE(„dateiname“;Tabelle1!A1);LÄNGE(ZELLE(„dateiname“;Tabelle1!A1))-FINDEN(„]“;ZELLE(„dateiname“;Tabelle1!A1)))
Grüße,
Martin
Kleine Ergänzung:
=RECHTS(ZELLE(„dateiname“;$A$1);LÄNGE(ZELLE(„dateiname“;$A$1))-FINDEN(„]“;ZELLE(„dateiname“;$A$1)))
Hallo bha,
danke für die Ergänzung. Damit ist wirklich alles in einer einzigen Formel drin, was man braucht.
Schöne Grüße,
Martin
Hallo vielen Dank für den Tipp 🙂
Leider werden die Gänsefüßchen aus den Kommentaren, dieser Webseite falsch interpretiert, daher kann die Formel nicht eins zu eins in Excel eingefügt werden.
Falls sich also jemand wundert warum die Formel nicht richtig funktioniert bzw. nicht aufgelöst wird, einfach die Formel von „bha“ verwenden und sich die Gänsefüßchen oben aus dem Artikel kopieren 😀
Hallo Martin, die Formel funktioniert perfekt.
ich habe aber eine Datei mit 23 Mappen die jeweils mit dem Datum eines wiederkehrenden Anlasses benannt sind. Nun habe ich das Problem, dass der Name der Mappe immer für alle der gleiche ist (wenn ich die Formel in einer Mappe eingebe, wird in allen Mappen das Datum angezeigt). Ich kriege es also nicht hin, in jeder Mappe den Namen separat anzuzeigen.
Hallo Martin, nichts für ungut für die Störung…. manchmal würde es helfen, die vorhergehenden Kommentare zu lesen. Somit wäre das Problem vielfach gelöst. hat top funktioniert. Genial
Irgendwie bekomme ich das ganze nicht in den Griff, nachdem ich eine ganz neue Datei geöffnet und als Inhaltsverzeichnis gespeichert habe komme ich nicht auf meine Arbeitsblätter um sie anzeigen zu lassen. Im der anderen Datei zeigt er mir nur den Namen des Reiters an. Wo mach ich bloß mal wieder einen Gedankenfehler!!!??
Gruß Joe
P.S. Martin du hattest mir mal dazu geschrieben aber ich verstehe es einfach nicht, sorry
Hallo Joe,
wenn ich Dich richtig verstehe, möchtest Du mit einem Klick auf den Namen im Inhaltsverzeichnis auf das jeweilige Arbeitsblatt springen?
Dann musst Du die oben gezeigte Formel noch in die HYPERLINK-Funktion einbetten. Zum Beispiel so:
=HYPERLINK(„#“&RECHTS(ZELLE(„dateiname“;$A$1);LÄNGE(ZELLE(„dateiname“;$A$1))-FINDEN(„]“;ZELLE(„dateiname“;$A$1)))&“!A1″)
Schöne Grüße,
Martin
Hallo Martin,
super Erklärung. Hat soweit geklappt. Leider bekomme ich den Hyperlink bei mir nicht hin.
=HYPERLINK(RECHTS(ZELLE(„dateiname“;’U09+ (WX166)‘!A1);LÄNGE(ZELLE(„dateiname“;’U09+ (WX166)‘!A1))-FINDEN(„]“;ZELLE(„dateiname“;’U09+ (WX166)‘!A1))))
Das steht momentan bei mir in der Formel. Das zweite Tabellenblatt (das nach dem Inhaltsverzeichnis) heißt U09+ (WX166), also nicht wundern!
Wenn ich nun auf den Hyperlink klicke kommt: Die angegebene Datei konnte nicht geöffnet werden.
Sieht jemand auf Anhieb meinen Fehler!
Danke für eure Hilfe!
Hallo Alexander,
es gibt drei Probleme in Deiner Formel: Durch die Leerzeichen im Blattnamen muss das Ganze in zusätzliche einfache Anführungszeichen gesetzt werden. Dann fehlt noch eine Zieladresse, die angesprungen werden soll. Und bei Sprüngen in andere Arbeitsblätter muss die Formel noch mit einem #-Zeichen eingeleitet werden. Versuch’s mal hiermit:
=HYPERLINK(„#'“&RECHTS(ZELLE(„dateiname“;’U09+ (WX166)‘!A1);LÄNGE(ZELLE(„dateiname“;’U09+ (WX166)‘!A1))-FINDEN(„]“;ZELLE(„dateiname“;’U09+ (WX166)‘!A1)))&“‚!A1″)
Grüße,
Martin
Hallo, ich würde diese Funktion gerne in einem Dokument mit 14 Blättern nutzen.
Ich habe das soweit eingefügt aber auf jedem Blatt erscheint dann der zuletzt geänderte Blattname und nicht der jeweilige Blattname.
Gibt es dafür eine Lösung?
Sorry… habe die Lösung gefunden die Formel ist dann.
=RECHTS(ZELLE(„dateiname“;$A$1);LÄNGE(ZELLE(„dateiname“;$A$1))-FINDEN(„]“;ZELLE(„dateiname“;$A$1)))