Contents
If in your Excel workbook the number of sheets has exceeded the second ten, then navigating through the sheets begins to become a problem. One of the beautiful ways to solve it is to create a table of contents with hyperlinks leading to the corresponding sheets of the book:
There are several ways to implement this.
Video
Method 1: Manually Created Hyperlinks
Insert an empty sheet into the book and add hyperlinks to the sheets you need using the command Insert – Hyperlink (Insert — Hyperlink). In the window that opens, select the option on the left Place in the document and set the external text display and the address of the cell where the link will lead:
For convenience, you can also create back links on all sheets of your book, which will lead back to the table of contents. In order not to manually create hyperlinks and then copy them to each sheet, it is better to use another method – the function HYPERLINK (HYPERLINK). We select all the sheets in the book where we want to add a back link (for mass selection of sheets, you can use the keys Shift and / or Ctrl) and in any suitable cell we enter a function of the following form:
=HYPERLINK(“#Table!A1″;”Back to TOC”)
This function will create in the current cell on all selected sheets a hyperlink with the text “Back to Table of Contents”, clicking on which will return the user to the sheet Table of contents.
Method 2: Dynamic Table of Contents Using Formulas
This is a slightly exotic, but very beautiful and convenient way to create an automatic table of contents sheet for your book. Exotic – because it uses an undocumented XLM function GET.WORKBOOK (GET.WORKBOOK), left by the developers for compatibility with older versions of Excel. This function unloads a list of all sheets of the current book into a given variable, from which we can then extract them and use them in our table of contents.
Open Name Manager tab formula (Formulas – Name Manager) and create a new named range with the name let’s say Table of contents… In field Range (Reference) enter the following formula:
=GET.WORKBOOK(1)
=GET.WORKBOOK(1)
Now in a variable Table of contents contains our search names. To extract them from there to a sheet, you can use the function INDEX (INDEX), which “pulls” elements from the array by their number:
=INDEX(Table of Contents, STRING())
=INDEX(Table of Contents; ROW())
Function LINE (ROW) gives the number of the current row and, in this case, is needed only in order not to manually create a separate column with the ordinal numbers of the elements to be retrieved (1,2,3…). Thus, in cell A1 we will get the name of the first sheet, in A2 – the name of the second, and so on.
Not bad. However, as you can see, the function gives not only the name of the sheet, but also the name of the book, which we do not need. To remove it, use the functions ZAMENIT (SUBST) и TO FIND (FIND), which will find the closing square bracket character ( ] ) and replace all text up to and including that character with an empty string (“”). Let’s open again Name Manager from the tab formula (Formulas — Name Manager), double click to open the created range Table of contents and change its formula:
=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)
=SUBST(GET.WORKBOOK(1);1;FIND(«]»;GET.WORKBOOK(1));»»)
Now our list of sheets will look much better:
A slight side difficulty is that our formula in the named range Table of contents will be recalculated only when you enter, or when the book is forced to recalculate by pressing a keyboard shortcut Ctrl + Alt + F9. To get around this unpleasant moment, we add a small “tail” to our formula:
=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)&T(TDATE())
=SUBST(GET.WORKBOOK(1);1;FIND(«]»;GET.WORKBOOK(1));»»)&T(NOW())
Function The TDATA (NOW) gives the current date (with time), and the function Т turns that date into an empty text string, which is then glued to our sheet name using the concatenation (&) operator. Those. the sheet name does not actually change, but since the function The TDATA is recalculated and gives a new time and date with any change in the sheet, then the rest of our formula will have to be recalculated too, and – as a result – the sheet names will be updated constantly.
To hide errors #LINK (#REF), which will appear if we copy our formula with the function INDEX for more cells than we have sheets, we can use the function IFERROR (IFERROR), which catches any errors and replaces them with an empty string (“”):
And finally, to add “live” hyperlinks to the sheet names for quick navigation, you can use the same function HYPERLINK (HYPERLINK), which will form the address for the transition from the sheet name:
Method 3. Macro
And finally, you can use a simple macro to create a table of contents. True, you will have to run it every time you change the structure of the book – unlike Method 2, the macro itself does not track them.
Open the Visual Basic Editor by clicking Alt + F11 or by choosing (in older versions of Excel) from the menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor). In the editor window that opens, create a new empty module (menu Insert – Module) and copy the text of this macro there:
Sub SheetList() Dim sheet As Worksheet Dim cell As Range With ActiveWorkbook For Each sheet In ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1" cell.Formula = sheet.Name Next End With End Sub
Close the Visual Basic Editor and return to Excel. Add a blank sheet to the book and place it in first place. Then click Alt + F8 or open the menu Service – Macro – Macros (Tools — Macro — Macros). Find the created macro there SheetList and run it for execution. The macro will create a list of hyperlinks with sheet names on the first sheet of the book. Clicking on any of them will take you to the desired sheet.
For convenience, you can also create back links on all sheets of your book, which will lead back to the table of contents, as described in Method 1.
- What is a macro, how to create it, where to copy the macro text, how to run a macro?
- Auto Generate Book Table of Contents with One Button (PLEX Add-on)
- Sending emails using the HYPERLINK function
- Quick jump between sheets in an Excel workbook