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)
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.
RichHolton Brilliant, after a lot of convoluted attempts (all beyond my knowledge though tbf), this worked a treat 👍