Like and see, form a habit; Words without words, deeds without far.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
It’s important to have a table of contents when there are so many worksheets in Excel that it can be difficult to find them every time. Today I’m going to introduce you to three methods of directory creation in Excel.
1. HYPERLINK function
The HYPERLINK function is used to jump to another location in the current workbook, either a cell or a region of cells. You can also open local files or online addresses. When you click the cell that contains the HYPERLINK function, Excel jumps to the specified location or opens the specified document.
Syntax: HYPERLINK (path and filename of the document to open, jump text displayed in cells);
For example, let’s add the following Excel to the directory:
When we click jump column jump to Shanghai jump to Shanghai worksheet:
When you click the back directory in the Shanghai worksheet, jump back to the HYPERLINK function worksheet.
First enter the formula “=HYPERLINK(“#” &b3&”! A1 “,” jump to” &b3) “in the B3 cell of the HYPERLINK function worksheet, and then fill down:
Then select all worksheets except the HYPERLINK worksheet. You can select the Shanghai worksheet first, then hold down the Shift key and click the last Guangzhou worksheet to complete the worksheet selection.
A1 =HYPERLINK(“#HYPERLINK function! A1 “,” return to directory “)
The effect is as follows:
If you feel that there are too many worksheets, one name entry is very troublesome, here to teach you a small skill.
Click “Define Name” in the command group under the “Formula” TAB to bring up the new name dialog box, enter the name of the directory, enter the formula for the reference position: “=GET.WORKBOOK(1)”, and press the “OK” button to complete the new name:
Then enter the formula in cell A1: “=INDEX(directory,ROW())” and fill down until an error occurs:
At this point we get all the table names and name them as: [workbook name]+ worksheet name. If we only want to keep the worksheet name, we can select the column A data area, then copy, then Paste and optionally paste as A value, so that the formula in the cell is first removed and only the text is left. Then remove the workbook name by finding and replacing it, leaving only the worksheet name as follows:
2. Compatibility
As the name suggests, Excel’s compatibility check feature identifies which cells in the current workbook use features not supported by previous versions of Excel, and then automatically generates reports, which we then beautify into directories. So let’s see how that works.
First select all worksheets, then enter the formula “= XFD1” in the E1 cell:
XFD1 represents the first row cell of XFD column, whereas earlier Excel only supported 256 columns at most, and XFD columns were already larger than 256.
Then click the “Files” TAB and select “Check Compatibility” in the “Check Problems” drop-down list under “Information” :
Then click the Copy to New table button:
Finally, the following report will be generated:
Then on this basis to complete the directory production, simple landscaping:
Let’s take a look at the process:
3. Document management
Excel can not only create a worksheet directory, but also create a file directory, directly open folders and various files, such as Excel, Word, PDF, pictures and so on.
Let’s prepare a few files locally:
Then record the file path in cell B3:B9:
Then just type in the formula: “=HYPERLINK(B3,” open “)” in the C3 cell and fill it down. Let’s look at the results:
Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.