Skip to main content

How to create table of contents for all sheets and updates automatically?

Supposing, you have a workbook which contains hundreds of worksheets, to navigate to a specific sheet among multiple sheets may cause headache for most of us. In this case, to create a table of contents for these worksheets may help you to go to the certain sheet quickly and easily. This tutorial will talk about how to create a table of contents for all sheets and update the table automatically when inserting, deleting or changing the sheet names as well.

Create a table of contents for all sheets and updates automatically with formulas

Create a table of contents for all sheets and updates automatically with a VBA code

Create a table of contents for all sheets and updates automatically with a powerful feature


Create a table of contents for all sheets and updates automatically with formulas

In Excel, you can create a table of contents by defining a range name, and referencing the name in a formula, please do with the following steps:

1. Insert a new sheet before all sheets where you want to create the table of contents and rename it as you need.

2. Then, click Formula > Define Name, see screenshot:

3. In the New Name dialog box, specify a name Sheetlist into the Name box, you can change it to your own, and then enter this formula: =GET.WORKBOOK(1)&T(NOW()) into the Refers to textbox, see screenshot:

4. Then, click OK button to close the dialog box.

5. Now, please go to the new sheet where you want to create a table of contents, and then enter the following formula into a blank cell:

=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")
Note: In the above formula, Sheetlist is the range name you created in step 2.

6. And then drag the fill handle down to the cells until blank cells are displayed, and all sheet names (including the hidden sheets) of the current workbook have been listed as below screenshot shown:

7. Then, you should create hyperlinks for the table contents, please use the below formula:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")
Note: In the above formula, A2 is the cell that contains the sheet name, and A1 is the cell where you want to jump to in that sheet. For example, if you click the hyperlink text, it will locate the cell A1 of that sheet.

8. Now, when clicking the hyperlink text, it will go to that sheet at once. And if you insert a new sheet, delete a sheet or change the sheet name, the table contents will be updated automatically.

Notes:
  • 1. With this method, all hidden sheets are displayed into the table contents as well.
  • 2. You should save the file as Excel Macro-Enabled Workbook format, in this way, when you open this file next time, the formulas can work well.

Create a table of contents for all sheets and updates automatically with a VBA code

Sometimes, you needn’t display the hidden sheets in the table contents. To solve this problem, the following VBA code can do you a favor.

1. Insert a new sheet before all sheets where you want to create the table of contents and rename it as you need. Then, right click the sheet tab, and choose View Code from the context menu, see screenshot:

2. In the opened Microsoft Visual Basic for Applications window, copy and paste the below code into the sheet code window:

VBA code: Create a table of contents for all sheets and updates automatically

Private Sub Worksheet_Activate()
‘Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
    If xWsh.Name <> xStrWShName Then
        If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
            Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
            Me.Range(xStrTCHeader).Offset(xI).Value = xI
            xI = xI + 1
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Then, press F5 key to run this code, and the table contents are created at once in the new worksheet, all hidden sheets will not display in the table contents, see screenshot:

4. Form now on, when deleting, inserting or renaming sheet, the table contents will be updated dynamically.

Note: You should also save the Excel file as Excel Macro-Enabled Workbook format to keep the code running successfully when you open the workbook next time.

Create a table of contents for all sheets and updates automatically with a powerful feature

If you have Kutools for Excel, its Navigation pane can help you list all sheet names into a left pane vertically, and navigate to the specific sheet quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Navigation, see screenshot:

2. In the expanded Navigation pane, click Workbook & Sheet icon, all opened workbooks are listed in the top list box, and all visible sheets within the workbook are displayed in the bottom list box, see screenshot:

3. And now, you can go to the sheet just by clicking the sheet name from the left pane. And when you delete, insert or rename a sheet, the sheet list in the pane will be updated dynamically.

Tips: By default, the hidden sheets don’t be shown in the navigation pane, if you want to display the hidden sheets, you just need to click the Toggle to unhide / hide all hidden worksheet(s) icon, click this icon again to hide the hidden sheet immediately.

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations