Shulou(Shulou.com)11/24 Report--

Original title: "can Excel also create directories?" This trick is so simple that there are no friends! "

Hello, everyone. I am the one who has been studying hard recently.

Recently, while combing through the knowledge points of Excel, I inadvertently raised a question:

There is directory navigation in Word, and we can jump to the corresponding document location by clicking on the title.

In PPT, we can add a new section, click on the corresponding section, we can jump to the corresponding PPT page.

Why is there no such thing as navigation directory for worksheets in Excel?

Excel also has a navigation bar, but when there are a large number of worksheets, it is too troublesome for us to find the specified worksheets!

Then I thought, since it's not in Excel, can we create our own catalog page?

So according to this idea, I made a table of contents page like this:

This article introduces two common methods of creating hyperlink directories:

Create a table of contents page using the hyperlink function (applicable to all versions)

Create a table of contents page with compatibility check (for Office 2003 and above)

Let's just follow my train of thought and continue to look back.

Now that there is the following worksheet in the workbook, we need to create a worksheet directory in the catalog page.

01. Before using the hyperlink function to create a catalog page, we need to get the worksheet name.

It is also possible to enter worksheet names one by one, but it is a bit troublesome. I will first introduce two ways to get worksheet names.

The first step of ▋: get the worksheet name. Here we introduce two methods. ❶ method 1: using square lattice function library

We have introduced the square lattice function library before, and here we use the GetSheetName function in the function library.

Official account background reply: function library, you can get the download link ~

The syntax rules for the GetSheetName function are:

= GetSheetName (serial number, [ignore hidden table]))

For example:

= GetSheetName (1), which is the name of the first table

= GetSheetName (2), which is the name of the second table

……

We can use the ROW function to get consecutive sequence numbers.

We enter the formula in the A2 cell of the catalog page:

= IFERROR (GetSheetName (ROW (A2)),) drop-down fill, and all the worksheet names come out.

❷ method 2: custom function formula.

In addition to using the function library, we can also write a custom function ourselves.

① hold down the shortcut key [Alt+F11] to open the VBA editor and right-click to insert a module.

② clicks on the module to copy the VBA code into the editor.

Function getName (ByVal sheet_no As Integer) getName = Worksheets (sheet_no). Name 'this means: Worksheets (1). Name, get the name of the first worksheet, Worksheets (1). Name, get the name of the first worksheet, and so on. End Function

Now that there is a GetName function in the worksheet, we can just use it.

Note:

If you use custom functions, the document needs to be saved in xlsm / xls format.

If you don't want to change it, you can directly get the worksheet name and copy and paste the name as a value.

At this point, we get the name of the worksheet, and then we directly use the hyperlink function to create a directory ~

Step 2 of ▋: use the hyperlink function to create the basic syntax of the hyperlink function:

= HYPERLINK (address, [friendly name])

I guess there must be a partner who will directly write the formula like this:

= HYPERLINK (A2 & "! A1", A2) but click on, the result will be an error, because when the HYPERLINK function references the cell, the first parameter needs to be preceded by a # sign.

Together, we can write the formula directly:

= HYPERLINK ("#" & A2 & "! A1", A2)

At this point, the catalog page is roughly done.

For the beautification of the catalog page, we can set the worksheet name in column A to white, ununderline column C, and change the color.

Use compatibility check to create catalog page PS: about compatibility check: Office can check the compatibility of documents with other versions of Office for Mac and Windows versions of Office, and create compatibility reports.

You can open the compatibility report to learn more about any compatibility issues and try to fix them.

Let's first take a look at the specific operation!

▋ first step ❶ selects all worksheets except the table of contents page.

Click the second worksheet, hold down the [Shift] key without releasing, and click the last worksheet.

❷ enters in the A1 cell:

= XDF1

❸ hold down the shortcut key [Ctrl+Enter] to batch fill.

Step 2 of ▋: open the compatibility check and copy the generated directory link to the C2 cell on the catalog table. ❶, select the File tab.

❷ selects "Information"-"check problems"-"check compatibility".

❸ pops up the compatibility Checker dialog box, selects copy to New Table, and a worksheet with the name "Sheet2" appears.

Step 3 of ▋: copy the generated connection area to the table B2 cell of the catalog page and beautify the catalog page through replacement and font formatting. ❶ copies the linked area.

❷ pastes the range into the C2 cell of the catalog page.

❸ select the area, hold down the shortcut key [Ctrl+H] to bring up the replacement window, and replace all'! A1 'with (empty).

❹ removes underscores, changes font colors, and modifies cell borders.

At this point, the table of contents page is complete, and a little finger appears over the text. After clicking, you can jump to the corresponding worksheet. Finally, delete the Sheet2 worksheet directly.

Step 4 of ▋: add jump links to work other than the catalog page. Select a worksheet other than the catalog page.

Enter the formula in the A1 cell:

= HYPERLINK ("# catalog page! A1", "return to catalog page") [Ctrl+Enter] batch fill, set the font to bold green font.

Finally, by simply beautifying the directory, you can make the effect shown at the beginning.

Seeing this, you must have some little question marks?

What does XDF1 mean?

We click on the A1 cell, hold down the shortcut key [Ctrl+ →], and then jump to the last column, which is XFD1.

XDF1, like XFD1, is actually a cell with a column marked XDF and a row marked 1.

Enter the formula:

= column (XDF1) the result is 16334.

That is, the number of columns in XDF1 is 16334, and the largest column in the worksheet is XFD, which is 16384 columns.

Why enter = XDF1?

The maximum number of columns in a compatible format is 256 (IV) columns, and the maximum number of columns in the current format is 16384 (XFD) columns.

When we use the formula = XDF1 in the worksheet, check for compatibility issues.

Because the maximum number of columns in the compatibility format is 256and cannot reference a cell with 16334 columns, a window prompt appears.

By copying what we checked into the new table, we can see the hyperlink location in the corresponding prompt question.

So, in addition to referencing the cell XDF1, we can also reference cells between columns 256 (IV) and 16384 (XFD).

03 OK, finally, let's summarize the two methods introduced in this article:

Create a directory using the hyperlink function. Knowledge points involved:

❶ gets the worksheet name.

The custom function is written mainly using Worksheets (1). Name to represent the name of the first worksheet. You will find that VBA is not very difficult.

❷ uses the hyperlink function to create a hyperlink directory, in which the first parameter needs to be preceded by a # sign when jumping to a cell.

Create a directory using compatibility checks. Knowledge points involved:

The maximum number of columns for ❶ compatible formats (version 03) is 256 (IV), and for formats other than compatible versions is 16384 (XFD).

So we can use the reference cell to construct the compatibility problem, and when we check the compatibility problem, copy the new table and get the hyperlink we need to create the directory.

Learned today's tips, while others are still rummaging through the worksheet, you can find the designated worksheet in a second through the directory!

If you often use Excel in your work, mastering these basic operations can help you greatly improve your efficiency!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.