How to Generate A List of All The Sheets In A Excel Workbook

Start with a workbook that has lots of worksheets or chart sheets. Then follow these steps to create a list of the sheet names:

1. Insert a new worksheet to hold the list of sheet names.
2. Choose Formulas➜Define Name to display the New Name dialog box.
3. Type SheetList in the Name field.
4. Enter the following formula in the Refers To field :

     =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

5. Click OK to close the New Name dialog box.

Note that this formula uses the GET.WORKBOOK function — which is not a normal worksheet function. Rather, it’s an old XLM-style macro function intended for use on a special macro sheet. Using an argument of 1 returns an array of sheet names, and each name is preceded by the workbook name. The REPLACE and FIND functions remove the workbook name from the sheet names. To generate the sheet names, enter this formula in cell A1, and then copy it down the column:

=INDEX(SheetList,ROW())

Figure above shows this formula in the range A1:A10. The workbook has seven sheets, so the formula returns a #REF! error when it attempts to display a nonexistent sheet name. To eliminate this error, modify the formula as follows:

=IFERROR(INDEX(SheetList,ROW()),””)

The list of sheet names will adjust if you add sheets, delete sheets, or rename sheets — but the adjustment doesn’t happen automatically. To force the formulas to update, press Ctrl+Alt+F9. If you want the sheet names to adjust automatically when the workbook is calculated, edit the named for mula to make it “volatile.”

=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)&T(NOW())

What good is a list of sheet names? Figure above shows a table of contents created by using the HYPERLINK function. The formula in cell B1 is

=HYPERLINK(“#”&A1&”!A1”,”Go to sheet”)

Clicking a hyperlink activates the worksheet and selects cell A1. Unfortunately, Excel doesn’t support hyperlinking to a chart sheet, so you get an error if a hyperlink points to a chart sheet.

Note : If you use this technique, you must save the workbook as a macro-enabled file (with an .xlsm or .xls extension).