[Important Edit: The definitive solution to the issue below is using CELL(“filename”,A1). The reference ensures the refresh to the current worksheet.]
As many of you might have found out, you can get the sheet name using the formula
The issue with this solution is however that the sheet name does not refresh immediately when changing sheet.
The following screenshots show how this as well as a proposed function you can add as add-in:
- Get Microsoft to add a new function “SHEETNAME”
- Add a custom function “SHEETNAME” yourself.
Create custom function “SHEETNAME
Create a new Excel File
Go to the VB-Editor (Alt-F11)
Create a new module containing the following code:
SHEETNAME = reference.Parent.Name
Go back to the normal Excel View
Now the new function “SHEETNAME” can be used.
It’s however reasonable to add an IFERROR to capture the error caused by not having the custom function – of course the updating issue of the standard method will occur.