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)
(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)
Lorenzo solution will not work if worksheet will be uploaded to Sharepoint and opened from browser:
"filename"
Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved.
Note: This value is not supported in Excel for the web, Excel Mobile, and Excel Starter.
Lorenzo As far as I can see, it's still the case that neither of these work within Excel for the Web (sorry for swearing...) which I unfortunately need to use in my work SharePoint environment. Presumably because the CELL() function and "filename" variable aren't available in EftW.
Most of the solutions I've seen suggested to date are workarounds based on extracting the sheet / tab name from the "filename" string - which won't work on EftW; or FORMULATEXT / SUBSTITUTE formulae which depend on extracting the sheet / tab name from cross-references between sheets, which I'm finding difficult to set up in such a way that those who aren't Excel gurus can replicate when new tabs are added in the future.
Can anyone explain why it would be so difficult for Microsoft (or someone whose knowledge of Excel is a lot greater than mine) to create a function which directly extracts a sheet name from wherever the tab text is stored within the file, rather than us having to resort to indirect workarounds? Something nice and simple like SHEETNAME(Sheet), where Sheet is the Sheet Number, or the current sheet if omitted...
IMHO the only thing we can do is upvote Create a function that returns the list of sheet name(s) in the workbook = SHEETNAME(), =SHEETNAMES() until there's enough to get MSFT's attention. Then, cross-fingers...
