Forum Discussion

Needsleep's avatar
Needsleep
Copper Contributor
Apr 16, 2023

how to reference the current sheet name in an function?

I am aware that you can reference other sheets in a function, but how do you reference the current sheet in one?

 

IF(Current_sheet_name="user template", value_if_true, value_if_false)

 

Thanks!

  • Needsleep 

     

    (the workbook must be saved)

    If you run Excel 365:

    =IF(TEXTAFTER(CELL("filename",A1),"]") = "user template", value_if_true, value_if_false)

    with other versions:

    =IF(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))) = "user template", value_if_true, value_if_false)

     

  • RichHolton's avatar
    RichHolton
    Copper Contributor

    Needsleep 

     

    There is a subtle weakness to all of the solutions so far: they all assume that the filename and path do not contain the right square bracket ("]"). But in both Windows and Mac (OS X and later), filenames and paths can contain that character.

     

    However, with the new text functions in Excel 365 and Excel for the Web, you can fix this weakness and greatly simplify the formula:

    =TEXTAFTER(CELL("filename",A1),"]",-1)

    The TEXTAFTER function can be very useful for this sort of thing.

     

     

    • dti65's avatar
      dti65
      Copper Contributor

      RichHoltonHowever the new functions in Excel solve a lot, there is still a certain weakness in this formula, if you have multiple worksheets of which you'd want to use the  name, excel states the name of the last active worksheet is used all over the workbook. Better would be when Microsoft would add "worksheetname" in the possible list of "infotypes" and makes is worksheet dependent.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      RichHolton 

      Nope, the question was if we are in right sheet or not. All answers assume we have "]" at the right. Compare your formula with one from Lorenzo above.

      • RichHolton's avatar
        RichHolton
        Copper Contributor
        The assumption is not only that we have "]" at the right. It's that the first instance of "]" is the separator between the path/filename and the sheet name.
        But it's possible to have a path and/or filename that contains the "]" character. So, CELL("filename") could return something like
        "D:\reports\reports [for review]\July report [new].xlsx]user template".
        With that path/filename, the formulas in Lorenzo above will return the sheet name as
        "\July report [new].xlsx]user template",
        which would not be equal to "user template".

        My suggested formula instead searches for the final "]". Since Excel doesn't allow that character in sheet names, the final "]" will always be the separator between the path/filename and the sheetname, regardless of any that might be in the path/filename.
    • LiamSinton's avatar
      LiamSinton
      Copper Contributor

      RichHolton Brilliant, after a lot of convoluted attempts (all beyond my knowledge though tbf), this worked a treat 👍

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Needsleep 

    You can use the CELL function to reference the current sheet name in an Excel function.

    Here’s how:

    1. Click on the cell where you want to reference the current sheet name.
    2. Type =CELL("filename") in the formula bar and press Enter.
    3. The result will be a string that contains the full path and name of the workbook, followed by a square bracket, followed by the sheet name in quotes. For example: [Book1.xlsx]Sheet1.
    4. To extract just the sheet name from this string, you can use the MID function. Here’s an example formula that returns the current sheet name: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255).
    5. Now you can use this formula to reference the current sheet name in your function like this: =IF(MID(CELL(\"filename\"),FIND(\"]\",CELL(\"filename\"))+1,255)=\"user template\", value_if_true, value_if_false).

     

    I hope this helps! 

    • RickSeiden's avatar
      RickSeiden
      Copper Contributor

      NikolinoDE 

      If you use the CELL function in more than one sheet in your workbook, they will all update to the match the last one you use.

      So if you have Sheet1 and Sheet2 and put

       

      =CELL("filename")

      in Sheet1!A1 it will show "Sheet1" in that cell.  Then if you go to Sheet 2 and put the same thing in cell Sheet2!A1, it will show "Sheet2" there.  But if you then went back to Sheet1, Sheet1!A1 will now show "Sheet2".

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    Needsleep 

     

    (the workbook must be saved)

    If you run Excel 365:

    =IF(TEXTAFTER(CELL("filename",A1),"]") = "user template", value_if_true, value_if_false)

    with other versions:

    =IF(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))) = "user template", value_if_true, value_if_false)

     

    • Aurimas444's avatar
      Aurimas444
      Copper Contributor

      Lorenzo solution will not work if worksheet will be uploaded to Sharepoint and opened from browser:

      "filename"

      Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved.

      Note: This value is not supported in Excel for the web, Excel Mobile, and Excel Starter.

    • JimS54's avatar
      JimS54
      Copper Contributor

      Lorenzo As far as I can see, it's still the case that neither of these work within Excel for the Web (sorry for swearing...) which I unfortunately need to use in my work SharePoint environment. Presumably because the CELL() function and "filename" variable aren't available in EftW. 

      Most of the solutions I've seen suggested to date are workarounds based on extracting the sheet / tab name from the "filename" string - which won't work on EftW;  or FORMULATEXT / SUBSTITUTE formulae which depend on extracting the sheet / tab name from cross-references between sheets, which I'm finding difficult to set up in such a way that those who aren't Excel gurus can replicate when new tabs are added in the future.

      Can anyone explain why it would be so difficult for Microsoft (or someone whose knowledge of Excel is a lot greater than mine) to create a function which directly extracts a sheet name from wherever the tab text is stored within the file, rather than us having to resort to indirect workarounds? Something nice and simple like SHEETNAME(Sheet), where Sheet is the Sheet Number, or the current sheet if omitted...

  • tomspoors's avatar
    tomspoors
    Copper Contributor

    Needsleep 

    This worked for me:

     

    =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

    The right square bracket appears only once in the returned filename string.

     

    Tom

  • sethkirk26's avatar
    sethkirk26
    Copper Contributor

    Here is a much more clean solution.   Uses the textafter() function.

    Referencing Cell C12.

    =TEXTAFTER(CELL("filename",C12),"]")

Resources