Excel2007怎么创建多个目录列表

We often save similar Excel worksheets in the same document for easy reference and viewing. However, it can become difficult to find a specific worksheet when a document contains a large number of worksheets. Creating a "directory" worksheet that displays all worksheet names and links can simplify this process.

Below is a method for quickly creating a worksheet directory for Excel workbooks.

Define Names

Open Excel 2007, right-click on the first worksheet tab, select "Rename," and rename it to "Directory" worksheet. Select cell B1, switch to the "Formulas" tab, click on "Define Name," enter the name "Worksheet Name" in the "New Name" window, enter the formula =INDEX(GET.WORKBOOK(1),$A1)& T(NOW()) in the reference box, and click OK to define a name "Worksheet Name" (see figure 1).

The formula contains GET.WORKBOOK(1) to extract all worksheet names in the current workbook, and the INDEX function determines which worksheet name to display based on the number in A1. Additionally, to ensure that the formula recalculates automatically when data changes, we use the NOW() volatile function, which forces recalculation. The T() function is used to convert the output of NOW() to empty text to avoid affecting the original formula result.

Note: The GET.WORKBOOK macro function cannot be directly used in a cell formula; it must be defined as a name to work properly.

Directory Setup

Enter 1 in cell A1 of the "Directory" worksheet and enter the formula =IFERROR(HYPERLINK(Worksheet Name & "!A1",RIGHT(Worksheet Name,LEN(Worksheet Name)-FIND("]",Worksheet Name))),"") in cell B1. The formula creates a hyperlink pointing to "Worksheet Name!A1" and shows the "Worksheet Name" when the value of the name "Worksheet Name" is not an error value.

Next, select cells A1:B1, drag the fill handle at the bottom right corner of the selection downwards to fill the formula and numbering down to 300 rows. This will automatically list all worksheet directories in columns A and B (see figure 2). Clicking on a worksheet name will quickly switch to that worksheet. Adjust the number of rows filled based on the maximum number of worksheets you have; generally, 300 rows should be sufficient.

Save Settings

Switch to the "Home" tab and adjust the font, font size, color, etc., in the directory for better visibility. Increase the font size, bold the text, and adjust the column width of columns A and B to display worksheet names completely. Right-click on other worksheet tabs and select "Delete" to remove all other worksheets, keeping only the "Directory" worksheet. Finally, click on the "Office" button, select "Save As," choose the file type as "Excel Macro-Enabled Template (*.xltm)," name the file "Directory.xltm," save it to C:Program FilesMicrosoft OfficeOffice12XLSTART folder, and close Excel 2007. Make sure to adjust the file path if your Office is not installed in the default location.

Create Directory in Three Seconds

In the future, creating a directory for a workbook will be simple. Just open the workbook you want to create a directory for in Excel 2007, right-click on the first worksheet tab, select "Insert," double-click on "Directory" in the "Insert" window to insert a "Directory" worksheet before the first worksheet. This will display all worksheet directories. This process should only take 3 seconds, right?

In the "Directory" worksheet, you can filter, sort, and search the directory to quickly find a worksheet name and click on the name to open the corresponding worksheet. After creating the directory, if new worksheets are added, deleted, or renamed in the workbook, the worksheet directory in the "Directory" worksheet will automatically update. If the total number of worksheets exceeds 300, the additional worksheet names will not be displayed, and you will need to copy and fill the formula in cells A1:B1 of the "Directory" worksheet for more rows.

Since the macro function GET.WORKBOOK(1) relies on macro functionality, any document with an inserted worksheet directory must be saved in "Excel Macro-Enabled Workbook (*.xlsm)" format to display the worksheet directory correctly when opened. Additionally, when opening the workbook, Excel 2007 will default to disabling macros. You need to click on the "Options" button in the warning bar, select "Enable this content," and confirm to display the worksheet directory.