Home » Blogs » Nick's blog

19. Excel Tips - Function to return the worksheet name

Nick's picture
Submitted by Nick on 8 March, 2009 - 21:27


There isn't a single function to return the sheet name, but you can use a combination of functions to get at it.

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))

- Here's a breakdown of what it's doing:

Sheet Name example

 

Training Video on getting Worksheet Name:

AttachmentSize
WorkSheet-Name-Workaround.xls32 KB
»

Function to return the sheet name: Tip #19

Submitted by Anonymous on 10 June, 2009 - 21:56.

Hi!

Love your site!! However, I couldn't make this tip work properly. First, perhaps you'd like to tell your viewers that unless you save a workbook first, the CELL() function won't work.

OK, so I saved my workbook. Now, let's suppose I have 10 sheets in the workbook. Hooray, I can now put their names into the text area, too. I've named Sheet1 "NumeroUno," and I use your very clever formula to insert the sheet name into Cell D3. Great! Now D3 says NumeroUno. Onto Sheet2. I name that one "NumeroDos." I want its name to appear in K4. I use the formula and K4 reads NumeroDos. Proudly, I go back and check the NumeroUno sheet. Oops! Its D3 cell says NumeroDos.

And so on. All 10 sheets display -- in their cell references -- only the name of the last sheet, even though each sheet has a unique name. How come?? A more to-the-point question: How can this be fixed?

Thanks!

Marge

»
Nick's picture

Worksheet name

Submitted by Nick on 10 June, 2009 - 22:25.

good spot... I hadn't noticed this bug before.

I have attached a workaround workbook to the original posting (WorkSheet-Name-Workaround.xls).

  • All you have to do is to calculate the cell containing the formula on activation of the worksheet.
  • Open the example file I have attached to the post
  • Press ALT + F11
  • Double click on "Sheet1"
  • Take a look at that code, and place it on any worksheet that contains the formula.

Code:

Private Sub Worksheet_Activate()
  Cells(2, 2).Calculate
End Sub

(assuming the formula is in B2)
 

 

 

»

Worksheet name

Submitted by Anonymous on 23 June, 2010 - 10:21.

Nick, I have used the workaround you suggest to refresh the data containing the worksheet name on opening the worksheet. Brilliant, solves the problem!

Problem is I want to reference the worksheet names in a separate summary worksheet, and that doesn't work if my formula on that summary worksheet gets the value from the worksheet containing the worksheet name, because it's not been refreshed!

Any suggestions?

Thanks very much
Peter, UK

»
Nick's picture

index page

Submitted by Nick on 24 June, 2010 - 04:53.

to create an index page, why not use this:

excelexperts.com/VBA-Tips-Create-An-Index-Page

... or you could add code to the worksheet with the summary that on selection recalculates all the other sheet formulae...

Application.calculatefull 

shld work

 

»