From the Not Just Numbers blog:

Sometimes it can be very handy in a spreadsheet with multiple sheets, to show the sheet name in the sheet itself.

Say you have a sheet for each salesperson and new sheets are regularly being added and you name the sheets with the salesperson’s names and want the same name to appear in the heading on the sheet. This is particularly important when printing the sheets as the worksheet name will not be visible.

You could, of course, type the same name in the header as the name of the sheet, but this has a few disadvantages:

  1. It’s twice as much work
  2. You may forget, and have a different salesperson’s name on the printed reports as is being used to pay them for example. This kind of inconsistency can lead to all sorts of problems!
Fortunately, it is relatively easy to get at the sheet name in a formula.
We can use the CELL command to do most of the work. This allows you to pull information about the current cell. In this case we are going to use it to pull the file path (including the sheet name). This is done by entering “filename” as the info type argument in the CELL command as follows:
=CELL(“filename”)
This will show something like:

 

C:\Users\Glen\Documents\[MySpreadsheet.xlsx]SheetName
where MySpreadsheet.xlsx is the name of the spreadsheet and SheetName is the name of the sheet that contains the CELL function above.
As we just want the sheet name, we can use the fact that the workbook name is enclosed in square brackets as follows:
=MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,999)
This earlier post gives more information on both the FIND and MID functions.
Essentially, this returns the 999 characters starting at the position after it finds the “]” (the +1 ensures that we don’t start until the character after the “]”). I have simply used 999 to ensure that all of the characters after the “]” are returned. It will not return any extra characters, so will just return all of the characters after the “]”, i.e. the sheet name!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.