How to quickly create a worksheet directory for an Excel workbook

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."

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.

Directory settings

In the table of Contents worksheet, enter 1 in cell A1, and in B1 cell, enter the formula =iferror (HYPERLINK (worksheet name & amp;!). A1 ", right (sheet name, LEN (worksheet name)-find ("] ", worksheet name))," ". The formula indicates that when the value of the name Sheet name is an error value, it is displayed as blank "", otherwise it will be created to point to the sheet name! A1 and displays the sheet name.

Then select A1:b1 Cell, point the mouse to the fill handle in the lower-right corner of the selection, and hold down the left mouse button down to line 300, fill the formula and number with 300 rows, and automatically list all the worksheet catalogs in columns A and B (Figure 2). You can quickly switch to the worksheet by clicking the appropriate sheet name. Please refer to the maximum number of worksheets possible to determine the number of rows to fill down, generally 300 should be enough.

Note: Right (sheet name, LEN (sheet name)-find ("]", sheet name) in the formula is used to remove the previous contents of the "in Sheet name". If you don't mind showing the "[Book1.xlsx]" category before the worksheet name, you can simplify the formula in B1 to =iferror (HYPERLINK (worksheet name & amp; "!). A1 ", sheet name)," ".

Save Settings

Switch to the Start tab to set the font, size, and color in the table of contents, and so on, it is recommended to enlarge and set the font size to view, and adjust the column widths of a:b columns to fully display the sheet name. Then right click on the Other sheet tab to select Delete, and delete all other worksheets to keep only one table of contents. Finally click the Office button, select Save As, and in the saved as window that pops up, select the Save as Excel Macro-enabled template (*.XLTM) format, file name "directory. Xltm", and save to C:Program FilesMicrosoft Officeoffice12xlstart folder, close Excel 2007 exit. If your office is not installed on the default path, modify it by the actual installation path.

Three seconds to create a directory

It's easy to create a table of contents for a workbook in the future, just open the workbook in Excel 2007 that you want to create a table of contents right-click on the tab on the first worksheet to select Insert, and in the Insert window, double-click Select Table of Contents to insert a table of contents sheet before the first worksheet and display all worksheet catalogs. 3 seconds is enough for this operation, right?

In the table of Contents worksheet, you can quickly find the worksheet name by filtering, sorting, finding it, and then clicking the sheet name to open the worksheet. When you create a table of contents, add, delete, or modify the worksheet name in this workbook, and the worksheet directory on the table of Contents worksheet is automatically updated. In addition, we have only copied 300 rows of table of contents formula, so when the total number of worksheets is more than 300, the sheet name that is exceeded is not displayed, and the A1:B1 formula in the table of contents worksheet is copied down again to fill the row.

Because the macro table function Get.workbook (1) works through macro functionality, documents that have been inserted into the worksheet directory must be saved in the Excel macro-enabled Workbook (*.xlsm) format so that the worksheet directory is displayed the next time you open it. In addition, when you open a workbook, Excel 2007 disables the macros by default, and you can click the Options button in the Warning bar to select the Enable this content single option to display the worksheet directory.