Forum Discussion
how to reference the current sheet name in an function?
(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)
You can use the CELL function to reference the current sheet name in an Excel function.
Here’s how:
- Click on the cell where you want to reference the current sheet name.
- Type =CELL("filename") in the formula bar and press Enter.
- 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.
- 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).
- 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!
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".
That's why it shall be used with reference like in Lorenzo post.
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!
