Forum Discussion

Needsleep's avatar
Needsleep
Copper Contributor
Apr 16, 2023
Solved

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!
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Apr 17, 2023

    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)

     

RickSeiden's avatar
RickSeiden
Copper Contributor
Oct 25, 2023

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".

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 25, 2023

RickSeiden 

That's why it shall be used with reference like in Lorenzo post.

  • RickSeiden's avatar
    RickSeiden
    Copper Contributor
    Oct 25, 2023

    SergeiBaklan I changed my formula to

     

    =CELL("filename",A1)

     

    as shown in the other post you mention, and I see the same behavior.  But I tried the version with RIGHT and LEN instead of the TEXTAFTER version, and that works as expected.

     

    Thanks for pointing out my mistake!

Resources