Create a table of contents for Excel quickly display the names and links for all worksheets

We often keep the same kind of related Excel worksheets in the same document so that we can reference and view them among the tables. When the work in a document is expressed to a certain number, it becomes cumbersome to find the worksheet that you want. It would be a lot easier to create a table of contents worksheet showing the names and links of all the worksheets.

Here's a quick way to create a worksheet directory for an Excel workbook.

Definition name

Open Excel 2007, right-click the First sheet tab and select Rename to rename it to the table of Contents worksheet. Select B1 cell, switch to the Formulas tab, click Define Name, enter the name sheet name in the new name window that pops up, and enter the formula =index (get) in the reference location. Workbook (1), $A 1) &t (now), click OK to define a name named "Sheet name" (Figure 1).

Get.workbook (1) in the formula is used to extract all the sheet names in the current workbook, and the index function presses the number in A1 to determine the name of the sheet to display. In addition, since the macro table function Get.workbook (1) does not automatically recalculate when data changes, and now () is a volatile function, any changes are enforced, so we need to add the now () function to the formula to make the formula automatically recalculated. The function t () converts the value produced by now () to empty text to avoid affecting the original formula result.

Note: The macro table function, Get.workbook, cannot be used directly in cell formulas and must be defined to work.