Sheet Names From A Workbook

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.

1. Go to the Formulas tab. 
2. Press the Define Name button. 
3. Enter SheetNames into the name field. 
4. Enter the following formula into the Refers to field. =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") 
5. 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.

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.