Werkbladnaam in cel

Het kan handig zijn om de werkbladnaam weer te geven in een cel van dat werkblad. Bijvoorbeeld om in de eerste regel van een werkblad deze naam te kunnen gebruiken als titel van dat werkblad. Standaard heeft Excel geen functie om de naam van een werkblad in een cel te zetten, maar met een slimme combinatie van een drietal functies is dat wel mogelijk. Om in een cel van een werkblad de werkbladnaam weer te geven, kan de volgende functie gebruikt worden:

=DEEL(CEL("bestandsnaam";A1);VIND.SPEC("]";CEL("bestandsnaam";A1);1)+1;99)

(NB: Vervang bij Engelstalige Excel-versies DEEL door MID, CEL door CELL, bestandsnaam door filename en VIND.SPEC door SEARCH)

Deze functie geeft overigens een foutmelding als een bestand nog niet is opgeslagen en dan dus geen bestandsnaam heeft. Met de functie CEL(“bestandsnaam”;A1) wordt het volledige pad met de bestandsnaam inclusief de werkbladnaam weergeven. De naam van het bestand wordt hierbij weergegeven tussen blokhaken, waarna direct de werkbladnaam volgt. Met de functie VIND.SPEC wordt de positie van de sluitblokhaak bepaald, waarna met de functie DEEL de tekst wordt weergegeven vanaf deze positie plus 1 tot en met het eind. In feite wordt de tekst weergegeven met een maximale lengte van 99, maar aangezien een werkbladnaam in Excel maar uit maximaal 31 karakters mag bestaan, is dat ruim voldoende.

Er moet een celverwijzing toegevoegd worden aan de CEL-functie (in dit geval A1, maar dat mag ook een andere cel zijn) om daarmee zeker te stellen dat de bladnaam van het huidige werkblad wordt getoond. Als geen celverwijzing is opgenomen wordt de bladnaam van de laatst gewijzigde cel getoond en dat kan een cel zijn op een ander werkblad.

Als de functie op een Nederlandstalige versie wordt ingevoerd en daarna dit bestand wordt geopend in een versie van een andere taal, dan zal deze functie een foutmelding geven omdat ‘bestandsnaam’ niet wordt vertaald. Om dat te voorkomen kan beter in plaats van ‘bestandsnaam’ voor ‘filename’ gekozen worden. Hoewel deze optie niet gekozen kan worden in het keuzemenu dat verschijnt wanneer de CEL-functie wordt ingevoerd, kan dit wel gewoon gebruikt worden. De optie met ‘filename’ werkt in alle taalversies.

 

Interesse in andere tips & trucs? Ga dan naar ons tips&trucs-overzicht of neem een kijkje op onze pagina met tutorials.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

29 reacties op Excel werkbladnaam in cel

  • Hallo,

    ik probeer de formule voor werkbladnaam in cel toe te passen. Helaas lukt het mij niet. Ik wil in cel B3 de werkbladnaam laten zien.

    Met vriendelijke groeten,
    Michiel

    • Hoi Michiel,
      Zonder het bestand te zien is dat lastig te beoordelen. Een oorzaak kan zijn dat je het bestand niet het opgeslagen, want zoals beschreven werkt de formule alleen als het bestand is opgeslagen.

      • Hallo Manfred,

        het is al een bestaand bestand, het staat opgeslagen op een server, omdat meerdere mensen het gebruiken.
        Als ik de formule nu in cel B3 plak, krijg ik een foutmelding, in de formule op het “]” gedeelte.

        Groeten, Michiel

        • Hoi Michel,
          Je moet alle dubbele aanhalingstekens na het plakken even vervangen door nieuw ingetypte dubbele aanhalingstekens. Daarna zou het goed moeten gaan. Ik heb de opmaak van de formule op de website aangepast zodat vanaf nu plakken wel goed gaat.
          Groeten, Manfred

          • Hallo Manfred,

            Dat is ‘m!
            Het is gelukt.

            Bedankt.
            Groeten, Michiel

  • Super handig, bedankt!

  • Echt top dit! Ik heb een bestand met heeeeel veel werkbladen, vervolgens werkt de ctrl find niet =( dus ben ik handmatig alle nummers in de eerste cel gaan zetten.. tot ik er na een paar dacht, dit kan toch niet zo!
    Na even googlen kwam ik bij deze tip terecht, Thnx! Dit scheelt veel werk, heel veel werk..

  • Ik heb een nederlandse versie van Excel, en mijn werkblad al op meerdere manieren opgeslagen maar krijg dit niet voor elkaar! Nog andere tips die ik kan proberen?

    • Dit zou normaal gesproken toch goed moeten werken. Krijg je een foutmelding en zo ja welke? Je hebt toevallig toch niet het woordje bestandsnaam uit de formule vervangen door de werkelijke bestandsnaam? In de formule moet namelijk het woord bestandsnaam of filename letterlijk blijven staan en dit moet tussen dubbele aanhalingstekens staan.

    • Lange tijd kreeg ik het niet voor elkaar ieder werkblad automatisch te voorzien van de werkbladnaam in een cel. Door deze Know How werd ik opnieuw geinsprireerd. En ja hoor, het leek te lukken. Toen ik echter na opslaan en opnieuw inlezen van het document de werkbladnamen op de diverse werkbladen nakeek bleken ze allemaal de naam van het laatst opgeslagen werkblad te hebben.

      Marie: mocht dit het probleem zijn dat je hebt, probeer dan eens het volgende, dat bij mij de situatie gisteren oploste in de Excel van Office 360, versie 15.0.4875.1001:
      *. Ga naar Opties. In mijn geval via Bestand.
      *. Kies onderdeel Formules.
      *. Vind balk Berekeningsopties.
      *. Vind optie “Werkbalk berekenen”.
      *. Wijzig de waarde in Handmatig.
      *. Haal de vink voor “Werkmap herberekenen voor opslaan” weg.
      *. Druk op knop OK

      Let wel: hiermee worden je werkbladen niet meer automatisch bijgewerkt en moet je dus zelf op voor jou geschikte momenten op ShiftF9 drukken. Teven worden bij gebruik van ShiftF9 afhankelijke tabbladen niet bijgewerkt. Wil je dat dat wel gebeurt, druk dan op F9, hierdoor wordt het hele document bijgewerkt, waardoor plotseling ieder werkblad dezelfde werkbladnaam bevat, naar mijn idee is het laatste een abuis van de software maker. Ga vervolgens naar ieder afzonderlijk werkblad en druk bij ieder werkblad op ShiftF9, zo worden de juiste werkbladnamen weer in ieder werkblad geplaatst.
      Tip: gebruik op andere werkbladen niet de waardes van de werkbladnamen in de cellen, want dat gaat naar mijn idee altijd mis.

      • Dag Evert,
        Ik mijn Excel-versie kan ik dit gedrag alleen maar reproduceren als je de celverwijzing achter bestandsnaam weglaat. Dus twee keer over

        CEL("bestandsnaam")

        in plaats van

        CEL("bestandsnaam";A1)

        Zoals ik ook al aangeef in de beschrijving is de toevoeging van deze celverwijzing essentieel.
        Groet, Manfred

        • In Excel 2010: ‘DEEL’ vervangen door ‘MIDDEN’

          • Dag Timo,
            De functie MIDDEN is geen standaard Excel-functie. Je bent wellicht in de war met de Engelstalige MID-functie. Deze Engelstalige MID-functie is gelijk aan de Nederlandstalige DEEL-functie.
            Manfred

  • Beste Manfred,
    Ik heb een Excel bestand met ongeveer 40 werkbladen. Is er een mogelijkheid om afhankelijk van een ingevoerde celwaarde in het eerste werkblad, automatisch het bijbehorende werkblad te openen ?

    • Beste Chiel,
      Ja, dat is mogelijk. Je kunt bijvoorbeeld naast de invoercel een cel met een hyperlink maken welke dynamisch wordt samengesteld op basis van de invoerwaarde. Door te klikken op de hyperlink kom je dan automatisch in het gekozen werkblad terecht. Als het helemaal automatisch moet, dus zonder te klikken op een hyperlink, dan zal dat met een macro moeten.

  • kan het ook omgekeerd, dus de waarde die in een cel staat als bladnaam weergeven?
    vb: als ik in cel a1 de waarde “Tutorial” in voer dat dan het werkblad de naam “Tutorial” krijgt?

    • Dat kan alleen met VBA:

      Private Sub Worksheet_Change(ByVal Target As Range)
          ActiveSheet.Name = Range("A1").Value
      End Sub
  • hoe kan ik in 1 grafiek meerdere tabbladen verwerken en ook in grafiek verwijzing naar de tabbladen?

    • Daar is in zijn algemeenheid zonder voorbeeldbestand moeilijk wat over te zeggen. E.e.a. is afhankelijk van hoe de grafiek er uit moet zien en wat de wensen precies zijn.

  • Beste Manfred,

    De formule werkt, bedankt daarvoor.
    Nu zou ik graag de verwijzing niet naar het huidige tabblad hebben, maar naar een ander tabblad.
    Is dit ook te realiseren?

    • Beste Luuk,
      Jazeker kan dat. Neem dan in plaats van alleen celverwijzing A1, de complete verwijzing inclusief werkbladnaam op. Bijvoobeeld:

      =DEEL(CEL("filename";Blad2!A1);VIND.SPEC("]";CEL("filename";Blad2!A1);1)+1;99)
      • Werkt deze nog? ik heb hem geprobeerd in Excel 2016(de nederlandse versie) maar krijg hem niet werkend. ik wil graag het bladnaam van blad 2 laten zien.

        =DEEL(CEL("bestandsnaam";[Blad2]!A1);VIND.SPEC("]";CEL("bestandsnaam";[Blad2]!A1);1)+1;99)
        • Ja dat werkt ook in Excel 2016, maar er zit in fout in jouw formule. De blokhaken om de werkbladnaam horen daar namelijk niet te staan.

  • De formule werkt, echter als daarna het bestand wordt opgelagen als (je plaatst het op een andere schijf / in een andere folder) en je sluit het bestand werkt de formule niet meer wanneer je dat “opgeslagen-als-bestand” opent.
    Er verschijnt dan in de betreffende cel de fout: #WAARDE!

    • Dat is vreemd. Ik heb dit zelf even getest, maar bij mij gebeurt dat niet. Omdat ik dit gedrag niet kan reproduceren, is van afstand niet te bepalen wat in uw geval de oorzaak kan zijn.

  • Hoi,

    Is het andersom ook mogelijk? Dat de naam van het werkblad automatisch veranderd als er een (bepaalde) cel veranderd?

    • Dat kan alleen maar met een macro. In de basis ziet deze er als volgt uit:

      Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Address = "$A$1" Then
              ActiveSheet.Name = Range("A1").Value
          End If
      End Sub
  • Hoi,
    als ik in een cel de formule [=DEEL(CEL(“bestandsnaam”;A1);VIND.SPEC(“]”;CEL(“bestandsnaam”;A1);1)+1;99)] plaats wordt de tab naam in de cel gezet.
    Zodra ik de waarde van deze cel opneem in [=VERT.ZOEKEN(D4;weeknummers!F2:G53;2)] krijg ik de melding “#N/B” in de cellen.
    hoe kan ik dit verhelpen?

    • Hoi Berno,
      Ik begrijp niet helemaal hoe je dit precies wilt opnemen in de VERT.ZOEKEN-formule. Kun je aangeven in welke cel je de formule met de bladnaam geplaatst hebt en wat je precies met de VERT.ZOEKEN-formule wilt bereiken?

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.