In this post we’ll find out how to get a list of all the sheet names in the current workbook without using VBA.
This can be pretty handy if you have a large workbook with hundreds of sheets and you want to create a table of contents. This method uses the little known and often forgotten Excel 4 macro functions.
These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick we’re going to use one of these in a named function.
In this example, I’ve created a workbook with a lot of sheets. There are 50 sheets in this example so I was lazy and didn’t rename them from the default names.
Now we will create our named function.
- Go to the Formulas tab.
- Press the Define Name button.
- Enter SheetNames into the name field.
- Enter the following formula into the Refers to field.
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") - Hit the OK button.
In a sheet within the workbook enter the numbers 1,2,3,etc… into column A starting at row 2 and then in cell B2 enter the following formula and copy and paste it down the column until you have a list of all your sheet names.
=INDEX(SheetNames,A2)
As a bonus, we can also create a hyperlink so that if you click on the link it will take you to that sheet. This can be handy for navigating through a spreadsheet with lots of sheets. To do this add this formula into the column C.
= HYPERLINK ( "#'" & B2 & "'!A1", "Go To Sheet" )Note, to use this method you will need to save the file as a macro enabled workbook (.xls, .xlsm or .xlsb). Not too difficult and no VBA needed.
Video Tutorial
This video will show you two methods to list all the sheet names in a workbook.
- The first method uses a VBA procedure from this post.
- The second (skip to 3:15 in the video) uses the method in the above post.
Hi,
this is very helpful, but it would be great if you mention how to do this only for visible sheets?
Unfortunately, with this method it’s not possible.
The hyperlink doesn’t work when there are spaces in the worksheet names
Exactly…. any solutions ????
When there is a space you need to add single quotes around the sheet name. Just after the # and just before the !
=HYPERLINK("#'"&B2&"'!A1","Go To Sheet")Or you can simply rightClick either the left or right tab-horizontal-scroll arrow at the bottomLeft, and then click the worksheet name (from the simple vertical list) that you want to jump to.
That’s a great tip if you want to navigate between sheets, but won’t help if you want to list out all the sheet names.
This is exactly what I was looking for! It’s a major time saver when my client provides a workbook with 80 sheets and wanted the data from each combined into one sheet.
Good to hear it’s of use to you!
Very useful and helpful.
I have modified the index part though for my use.
Others might also find this helpful:
You can generate the list of sheet names directly without having to first create an index column by using the ROW() function.
I used:
=INDEX(SheetNames,ROW()-“row offset”+”sheet offset”)
where the “row offset” is the number of rows down the sheet you intend to start the numbering from and “sheet offset” is the number of sheets in from the start of the workbook you intend to start the list from.
An example is =INDEX(SheetNames,ROW()-2+2) from what I’ve done.
I start my sheet list on row 3 of a summary page and allowed the first two rows for headings etc. , and I skipped the first two sheets in the workbook. In this case ROW()-2+2 will be evaluated to be 3.
Maybe have a play with the numbers used in the row and sheet offsets and you will see what i mean.
Thanks Trevor, good tip!
Thanks! I had to switch the commas by semi-colons, but otherwise it’s fine! 🙂
I believe that’s due to regional version of Excel, maybe French?
Pliz, put the result.
Can’t get it to work
my excel end up with #N/A , please help me !! thanks
When the index is past the number of sheets in the workbook the formula will produce an #N/A error. You can wrap the formula in an IFERROR to avoid this.
Very helpful thank you.
Thank you so much John. You’re a life saver.
Wow. Just wow.
My list does not automatically update if the sheet is renamed. Is there a way to get around this?
Use the keyboard shortcut Ctrl + Alt + F9 to recalculate.
Hello, John,
My indexing and redirecting works fine until I invoke Ctrl + Alt + F9 to recalculate; then it shows #NAME errors!?
Same happens when I add a sheet to the workbook.
Can you, please help with this?
Zo
I fixed it by applying the names in the list through the “Define names” function as shown above here.
Simply typing the same that this formula produces didn’t work for me?!
Hello,
What would the formula be to then retrieve a ‘named cell’ value from one of the worksheets?
GOAL – list all names of sheets, then retrieve the value of a ‘Total’ cell from each sheet. Then I have a summary sheet where I can calculate a grand total of expenses from each worksheet.
Thanks so much!
You could use the INDIRECT function to create a reference to your total on each sheet, by referring to the sheet name and location.
But honestly, it sounds like you should reorganize your data so that you have one proper data set instead of data across many sheets. Then use a pivot table to do any analysis.
Thanks a lot that’s awesome!
How can I use this to reference cells in the other sheets?
Thanks a lot!
If your sheet name is in A1 and you want to reference a cell on that sheet in C2, use this formula:
=INDIRECT("'"&A1&"'!C2")Thanks a lot John! Much appreciated!
Thank you!! This worked perfectly for me.
Try this to combine the Hyperlink as part of the name… Also gets rid of any that aren’t used so you can make it a full page and add tabs later if needed.
=IFERROR(HYPERLINK(“#’”&INDEX(SheetName,A3)&”‘!A1″,INDEX(SheetName,A3)),””)
Thank you,
It was very helpful.
I tried this earlier on another sheet and it worked, but today i’m trying it again and i’m getting a “#REF!” error. When I step through the calculations, it appears to resolve the SheetName function to an array, follows by “(A2)”, which resolves to 1. Either I’ve got an elusive type-o or Excel has changed how it does this since the last time I tried. Any suggestions? I’m on office 365 so updates come whenever they feel like it.
Nevermind… I found my elusive Type-o! I had done SheetNames(A2) rather than Index(SheetNames,A2).
Doh!
John – am using a Mac with the latest version of Office. When I try to save the file with your REPLACE formula, I get a message saying that Excel 4.0 formula’s can’t be saved in a macro-free workbook.
Is there a workaround for this?
Thanks.
Try saving as a xlsm file.
Isn’t that a macro enabled file type? Thought the idea of this formula was to stay away from that.
They are a type of old legacy macro from before VBA, so an xls file will also work.
Here’s the modern way of doing this.
I used this formula for both Hyperlink and Sheet Name
=HYPERLINK(CONCATENATE(“#”,”‘”,INDEX(SheetNames,P1),”‘”,”!A1″),IFERROR(INDEX(SheetNames,P1),””))
Thanks! Good tip on combining them into one.
Would you share the file with the example? my excel is in spanish and GET.WORKBOOK doesn´t work because of language,
Sorry for the delay, here’s the link.
Thanks John for a valuable post!
i want all sheets single client name in one sheet, please tell me
I don’t want the name of the 1st sheet . I want to get the index start with 2nd sheet onwards. How do I do that ?
This is genius!! Thanks vm.
Will this work in Google Sheets?
I highly doubt it.
HI John, It worked well before and suddenly it does not work anymore and has a #Name? error. any idea about the problem?
Not sure. Maybe try deleting everything in the name manager and start over.
I’m probably too late to help you, but for others, I had a similar error of a spreadsheet working for years, then suddenly getting a bunch of #NAME? errors. Turned out macros were automatically being disabled, and the GET.WORKBOOK function relies on macros.
This is one of the best tip I have seen … Not only it lists the workbooks in one sheet but also provides hyperlink… such a saviour..!!!
Works like a charm. Thanks!
Note :- Formula Work only if your Sheet Name in Numeric
=”=’E:\Contacts\”&”[“&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”‘!$B$1048576″
Use this Formula To Get Value from other Excel Sheet
________________
Define Path = “=’E:\Contacts\”&”[”
Give Sheet Name [if Sheet Name in Numericl] = SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1
Cell No. = !$B$1048576