19. Excel Tips - Function to return the worksheet name
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:
Training Video on getting Worksheet Name:
| Attachment | Size |
|---|---|
| WorkSheet-Name-Workaround.xls | 32 KB |
»
- Nick's blog
- Login or register to post comments
- 98576 reads
thanks alot
thanks alot for this effort, i tried the index macro and it' great, however i have a problem that i have sheets name like "001" in the index sheet its appear "1". i adjust the cell format to be text but still the same problem. i need the sheet name appear as it "001". lods of thanks
sheet name expression great but I need more than that.
Nick,
That expression is great and I will use it but what if the sheet I need the name from is not the active sheet? For example, My current sheet is the first sheet but I need to verify the name of the 3rd sheet and put that name in a formula in the first sheet.
Thanks
as the 2nd argument of the
as the 2nd argument of the CELL function, enter the cell address of cell A1 on the target sheet
Worksheet name
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
index page
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
Function to return the sheet name: Tip #19
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
Worksheet name
good spot... I hadn't noticed this bug before.
I have attached a workaround workbook to the original posting (WorkSheet-Name-Workaround.xls).
Code:
Private Sub Worksheet_Activate()
Cells(2, 2).Calculate
End Sub
(assuming the formula is in B2)
Printing entire workbook using above functions
Thank you very much for the code.
I have tried and it worked perfectly.
When I want to print only current sheet it recalculates and the name of sheet is correct.
But when I tried to print entire workbook, again the same disadvantage - Excel shows in print preview last used worksheet name, not current. And also prints incorrectly.
May be there is another workaround of this problem?
I thought this was the answer, so disappointed!
I have been trying to reference sheet names for some time and it just didn't work. I just went through all your steps only to find exactly the same thing diana did: I can only print the active worksheet at any one time or my titles are all the same. I thought I used to do this but it may have been all the way back with lotus and then again if it was that far back maybe it was just the file name and not the sheet name.
I know I can use headers to put the sheet name on the page but it just isn't as pretty as my complex titles on the sheets themselves, which concatenate the sheet name and the file name (without path or extension) so I end up with something like: November 2013 Patient Delay Days formatted as a Title, followed by an appropriate subtitle and centered over the appropriate data, with my borders and shading all coordinated. Admittedly I can type each title in each sheet but I have neglected to update a single sheet in a workbook that has a sheet for each day of the month in the past and it ticks me off.
Obviously I am not the only one that thought this was a good idea. Is there another way to do this?
Alternative Solution
VBA macros can format and paste your sheet name and file name on each worksheet.
The simplest macro would loop all worksheets and paste the data in the same cell for each worksheet. Each worksheet would have its own name pasted in.
To paste the data into varying cell locations would require a smarter macro with a little more coding but once created it is reusable.
Since the data is pasted into the cell, there is no cell updating when changing sheets or modifying other cells.
If the workbook name changes then the macro must be rerun to refresh the data.
Tell me if you will be using the same cell address in each worksheet or need custom addressing.
Regards,
David