Excel Sheetname Function

[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!
image

Sheet 2: field A1 has not refreshed
image

Sheet 2: after a press of F9 A1 is recalculated (Note that setting the recalculation options to “automatic” does not help.)
image

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
image

Add the Add-In in Excel Options:
image

Browse for the file you created an add the Add-In
image

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
image

Advertisements