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.
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,
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")Thank you!! This worked perfectly for me.