[Important Edit: The definitive solution to the issue below is using CELL(“filename”,A1). The reference ensures the refresh to the current worksheet.]
Issue description
As many of you might have found out, you can get the sheet name using the formula
=MID(CELL(“filename”);FIND(“]”;CELL(“filename”))+1;99)
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:
Sheet 1: all good!
Sheet 2: field A1 has not refreshed
Sheet 2: after a press of F9 A1 is recalculated (Note that setting the recalculation options to “automatic” does not help.)
Proposed solutions
- 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:
Function SHEETNAME(reference)
SHEETNAME = reference.Parent.Name
End Function
Go back to the normal Excel View
Save As, pick Type “Excel Add-In (*.xlam)”, the path will change automatically, Save
Add the Add-In in Excel Options:
Browse for the file you created an add the Add-In
Now the new function “SHEETNAME” can be used.
Usage
=SHEETNAME(reference)
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.
=IFERROR(SHEETNAME(A1);MID(CELL(“filename”);FIND(“]”;CELL(“filename”))+1;99))
Sheet 1: without Add-in
Leave a Reply