Search Not Just Numbers

Loading...

Tuesday, 29 September 2015

Excel Tip: Display current sheetname

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".

4 comments:

  1. Yes, I use this quite a lot

    BUT!!

    You must include a cell reference from the sheet as the second part of the CELL function (it says it'a optional but don't believe it)
    Otherwise it will be the sheet name of the current sheet (and if you then print several together then they'd all have the same name!)
    Entering it as CELL("filename",A1) saves all the pain I endured

    If all you need to do is see the sheet name on the printout then there is an option for that on the Header/Footer section of the Page Setup

    ReplyDelete
    Replies
    1. Thanks. I'll keep an eye out for that one. I hadn't realised it did that!

      Delete
  2. The maximum length of a sheet name is 31 characters. So, no need for 999 as 31 would do.

    ReplyDelete
    Replies
    1. Thanks Gary, I lazily use 999! I don't think it makes a difference as ling as it is at least 31.

      Delete