Automatic worksheet/tabs list in Excel - the old way

Here’s the ‘old-fashioned’ way to make a list of worksheet tab names. It’s old because the formula uses ” Get.Workbook() ” which is an Excel 4 macro function that’s now blocked in modern Excel for security reasons. See Excel 4 macros are now blocked and about time tooWe’re including this older method for the sake of completeness and for users of older Excel

We’ve already talked about fitting more tabs on the screen or making a manual list of tabs/worksheets. Now we’ll take the next step and make an automatic list of worksheets that will update as the workbook changes.

Making a list of worksheets is a thing you might expect to be easy but is almost ludicrously intricate.  There’s no direct function to do it and the long0-standing method relies on a very old and officially obsolete Excel function (which has no modern equivalent for reasons passing understanding).

In this article we’ll explain some ways to make an automatic list of worksheets. At the end we’ll show the ‘old-fashioned’ method that won’t work in modern Excel.

The steps are straightforward, even if you don’t understand the functions and formulas involved. We’ll break it down so you can understand how the whole thing comes together.

The old-fashioned method

Here’s the ‘old-fashioned’ way to make a list of worksheet tab names. It’s old because the formula uses ” Get.Workbook() ” which is an Excel 4 macro function that’s now blocked in modern Excel for security reasons. See Excel 4 macros are now blocked and about time too

We’re including this older method for the sake of completeness and for users of older Excel.

The tricky bit is making the initial list of tab names. Create a Define Name with a function which grabs the list of worksheets and puts them into an array.  Go to Formulas | Define Name | Define Name …

Name:  a label for the Name.  We’re using SheetList

Refers to:  this is the magic bit.  Copy this formula:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Get.Workbook() is the essential part.  It’s an old Excel function that’s still necessary and available but not part of the current Excel function list.  You won’t find it in the Formulas tab but it works fine … with some conditions we’ll mention in a moment.

To test your new name type =SheetList into a cell.  The worksheet names will fill the cells to the right.

Gotchas

Let’s stop a moment to mention some of the little gotchas about this tip.  It’s widely quoted on the Internet having been tweaked and adapted over time by various Excel wizards.

Too often the setbacks of this approach aren’t mentioned. Those mostly arise because the formula uses an obsolete function that Microsoft hasn’t replaced in modern Excel releases.

Save as a macro workbook

Because Get.Workbook() is an old Excel 4.0 function it can’t be saved in a ‘macro-free’ .xlsx workbook.  If you try, an error appears.

Instead save as a macro-enabled workbook .xlsm .  That’s a problem in some situations where macro-enabled Office documents are restricted or outright blocked due to security/virus risks.

Only Excel for Windows

Obsolete functions like Get.Workbook only work in Excel for Windows.

Other Excel’s (Mac, Online and apps) will show nasty #NAME errors instead.

Hidden tabs included

Get.Workbook includes any hidden workbooks.  Here, the array example has the ‘Hide me away’ tab listed but it’s not on the tab list at the bottom of the worksheet.

All you can easily do is temporarily unhide any hidden tabs, move them to the end of the tabs list then rehide them.  At least they’re grouped together at the end of your tabs list.

Make the list automatically update

The formula, and the array list it makes, has a limitation.  It’s only refreshed when Excel thinks it’s necessary, which isn’t good enough in this case.  Changes to the tabs or names might not be quickly reflected in the array list.

To make the array update automatically needs an old Excel trick to force recalculations:

&T(NOW())

NOW() returns the current time and is automatically updated by Excel whenever it recalculates the worksheet.  T() is a test for a text value with the useful property of returning an empty string ( “” ) if the function contents are NOT text.  NOW() always returns a date/time (obviously) so &T(NOW()) will always add nothing to a string. Because the name formula has the NOW() function, Excel will recalculate the entire name whenever a recalc occurs.  In Excel parlance NOW() is a volatile function which makes the whole name formula volatile.

Go back to Formulas | Name Manager and change the SheetList name formula. Add &T(NOW()) at the end.

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") &T(NOW())

Making the list

Now we have an array list of the tabs you have many choices for presenting that list in a worksheet.

Start by putting this formula into the A2 cell

=INDEX(SheetList,ROWS($A$2:$A2))

• We’re assuming row 1 is for headings. If not use $A$1:$A1
• if you need to start in another cell or column, change the $A$2:$A2 reference accordingly.

Then click and drag the first cell down the column to clone it into other cells.  Stop when you’ve copied enough cells for the number of tabs.

If you’ve gone too far, #REF will appear. If there’s a chance that more worksheets will be added later, you might want to keep those error formulas in place.

Adding a clickable link to each tab

Make links in Excel with the Hyperlink() function.

Hyperlink(<link>,<text to display>)

The <link> can be to parts of the Excel workbook, another workbook, a standard web page link or many other things.  We’re just interested in links to each tab/worksheet like this.

Add this formula into column B

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

• We’ve added the work ‘Clickable’ in the example to show that the visible cell text can be anything you like. To see just the tab names use =HYPERLINK(“#'”&A2&”‘!A1”,A2)
• Again, change the cell reference as necessary.

Drag and copy the cell down, just like in the first column.

Putting it all together

Most likely you won’t need a separate list of tabs and clickable versions. Just one list of the worksheets that’s also clickable like this.

Here’s the formula for making a clickable list direct from the SheetList array.

=HYPERLINK("#'"&INDEX(SheetList,ROWS($A$2:$A2))&"'!A1",INDEX(SheetList,ROWS($A$2:$A2)))

We’ve changed the references to a worksheet name from “A2” (another cell in the current worksheet) with a reference direct to an element the SheetList array “INDEX(SheetList,ROWS($A$2:$A2))”

Yet again, drag and copy the first cell down