Tip 9: Generating a List of Sheet Names
Oddly, Excel doesn’t provide a direct way to generate a list of sheet names in a workbook. This tip describes how to generate a list of all the sheets in a workbook. Like the previous tip (“Generating a List of Filenames”), this tip uses an Excel 4 XLM macro function in a named formula.
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 (see Figure 9-1):
=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)
5. Click OK to close the New Name dialog box.
Figure 9-1: Using the New Name dialog box to create a named formula.
Note that this formula uses...