Wie heißt du? Den Namen des Arbeitsblattes in Excel ermitteln 15

Der Name eines Excel-Arbeitsblattes lässt sich mit der ZELLE-Funktion und einem kleinen Trick ermitteln.
 

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()

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

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

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

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

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

Alles in einer Formel

Das Praktische an der Geschichte: Der ausgegebene Name passt sich automatisch an, wenn das Arbeitsblatt umbenannt wird:

Automatische Anpassung inklusive

Automatische Anpassung inklusive

Ich gebe zu:
Eine etwas vertrackte Lösung, aber letztendlich doch mit den normalen Excel-Bordmitteln und ohne VBA-Programmierung.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

15 Gedanken zu “Wie heißt du? Den Namen des Arbeitsblattes in Excel ermitteln

  • Dietmar

    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?

    • Martin Weiß Beitragsautor

      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

      • Joe

        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

        • Martin Weiß Beitragsautor

          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

    • Martin Weiß Beitragsautor

      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

  • Alexander

    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 😀

  • Wilhelm

    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.

  • Wilhelm

    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

  • Joe

    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

    • Martin Weiß Beitragsautor

      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

      • Alexander

        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!

        • Martin Weiß Beitragsautor

          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

  • Patrick Mardorf

    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?

    • Patrick Mardorf

      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)))