An automatically updated list of all worksheets or tabs in Excel but there are ‘gotchas’ which aren’t often mentioned.
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 current method relies on a very old and officially obsolete Excel function (which has no modern equivalent for reasons passing understanding).
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.
Make a list of worksheet names
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:
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.
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:
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.
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
- 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
- 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.
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