Microsoft Office Tutorials and References
In Depth Information
Tip 9: Generating a List of Sheet Names
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):
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 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: