Excel case sharing: batch generation of hyperlinked directories and automatic updates

This article will introduce you to the GET.WORKBOOK function and share a case to see how to use this function to batch generate hyperlinked directories in Excel and automatically update them. Come and learn how to create worksheet directories in Excel!

At work, you may encounter an excel workbook with many worksheets, just like a book with many pages. At this time, if you can create A worksheet directory can not only display all worksheet names, but also click on the worksheet name to quickly jump to the specified worksheet page, which will greatly improve our work efficiency.

So, some cousins ​​started to do it. They manually used Excel to create directory links pointing to each worksheet. Finally, dozens of minutes later, they completed the creation...

This At this time, if the worksheet changes or is added, all the previous work will be in vain, and you will have to create and modify it again, which is time-consuming and labor-intensive.

Today I will share with you a very smart method on how to batch create directories with hyperlinks in Excel. No matter how the worksheet changes or is added, it can be automatically extracted and created, saving time and effort.

As shown below, there are 8 worksheets in the workbook. In order to quickly jump to the specified worksheet, we create a worksheet directory for it.

First create a new worksheet named "Table of Contents"

Select the "Formulas" tab and click " Define name".

A new name dialog box pops up, enter "Worksheet" for the name, and enter the formula for the reference position:

=GET.WORKBOOK(1)

<strong>GET.WORKBOOK</strong> function is a macro table function that can extract all worksheet names in the current workbook. Macro table functions cannot be directly used in cells. To use, you need to define a name before it can be used.

There is a defined name called "Worksheet" in the "Formula" tab-Name Manager.

At this time, enter the formula in cell A2: =INDEX(worksheet,ROW(A2))Drag down to fill in the formula and you can extract it Output the worksheet name.

Formula description: Use the INDEX function to reference all worksheet names in the definition name "worksheet". The second parameter uses ROW (A2) to indicate that extraction starts from the second worksheet name, because the first worksheet The table name is "Directory", which is a worksheet name we don't need.

You can see that the worksheet name extracted using the INDEX function has the workbook name, so we also need to improve the formula and replace the workbook name. Keep the worksheet name.

Improve the formula in cell A2 to:

=REPLACE(INDEX(worksheet,ROW(A2)),1,FIND("]",INDEX(worksheet, ROW(A2))),"")

Formula description: Use the REPLACE function to replace the workbook name with nothing. The replaced character position is the first one. Use the FIND function to find the number of replacements. ]" character position, and then replace it with nothing.

Finally enter the formula in cell B2:

=HYPERLINK("#"&A2&"!A1",A2) Drag down to fill in the formula.

Formula description: HYPERLINK is a function that can create shortcuts or hyperlinks. "#" indicates that the referenced worksheet name is in the current workbook, and "!A1" indicates that it is linked to the A1 unit of the corresponding worksheet. format, the second parameter A2 of HYPERLINK indicates that the hyperlink is named after the worksheet name.

The worksheet directory is now complete! If a worksheet is added or changed in the workbook later, we only need to drag down to fill in the formula to automatically extract the worksheet name and automatically create a hyperlink.

Because we use the macro table function, it cannot be saved in an ordinary table. You need to select "Excel macro-enabled workbook" in Save As, with the suffix name xlsm or save it as "Excel 97-2003 Workbook".

That’s it for today’s tutorial. After finishing it, do you feel that you have taken many detours in making forms? The countless nights we worked overtime were actually unnecessary~

Related learning recommendations: excel tutorial

The above is the detailed content of Excel case sharing: batch generation of hyperlinked directories and automatic updates. For more information, please follow other related articles on the PHP Chinese website!