Quickly create a table of contents for Excel 2007 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."




The
formula Get.workbook (1) is used to extract all the worksheet names in the current workbook, and the index function determines the name of the sheet to display by the number in A1. 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: Macro table functions Get.workbook, cannot be used directly in cell formulas, you must define names to work.





Directory settings



enter 1 in cell A1 on the table of Contents worksheet, enter the formula =iferror (HYPERLINK (worksheet name &!) in B1 cell. 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 the function is to drop the previous content in the sheet name. If you don't mind showing the "[book1.xlsx]" type of content before the worksheet name, you can simplify the formula in B1 to =iferror (HYPERLINK (worksheet name &!). A1 ", sheet name)," ".





Save Settings



switch to the Start tab to set the font, 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 directory for a workbook later, just open the workbook you want to create in Excel 2007, right-click on the tab on the first worksheet, select Insert, and then double-click Select Table of Contents in the Insert window to insert a table of contents worksheet before the first worksheet. and displays all the worksheet directories. 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 features, documents that have been inserted into a worksheet directory must be saved in the Excel macros-enabled Workbook (*.xlsm) format to display the worksheet directory 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.