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)
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 https://support.microsoft.com/en-gb/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b function can be very useful for this sort of thing.
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.
- 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.- Thanks for the explanation on this RichHolton!
