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:
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.”
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.