Microsoft Office Tutorials and References

In Depth Information

**Tip 9: Generating a List of Sheet Names**

Figure 9-2 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()),””)

Figure 9-2:
Using a formula to display a list of sheet names.

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

formula to make it “volatile.”

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

What good is a list of sheet names? Figure 9-3 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.