Forum Discussion
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!
(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)
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.
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.
Thanks for the explanation on this RichHolton!