List of Workbook Tables and References

John asked if there was a way to create a list of all table names in the workbook, along with the underlying table reference. In this post, I’ll demonstrate how this can be accomplished with Power Query and an Excel formula.

Objective

Before we get into the mechanics, let’s confirm our goal here. We have a workbook that has numerous tables stored on various worksheets. The tables were created using the Insert > Table command, and they look a bit like this:

Without using VBA, we would like to create a list of these tables along with the corresponding sheet/cell reference. While we are at it, we may as well make it easy to navigate to each table by setting up a hyperlink. Plus, we want it to be easy to update if anything changes … so maybe we need to right-click and refresh to pull in any new tables we may add down the road.

We basically want something like this:

We’ll let’s do this thing.

Video

Narrative

We’ll accomplish our goal in three steps:

• Get table names with Power Query
• Create the reference column with CELL
• Create the link column with HYPERLINK

We’ll take them one at a time.

Get table names with Power Query

To create the list of table names, we’ll use Power Query. Data > Get Data > From Other Sources > From Blank Query. In the resulting Power Query Editor window, we type the following formula (case sensitive) into the formula bar and hit Enter:

=Excel.CurrentWorkbook()

Power Query then displays a list of the tables, like this:

We don’t really need to carry the Content column forward, so we can delete it.

Also, the Power Query results table will actually be included in the query results next time we refresh, so, we can actually prevent it from appearing by setting up a filter now. (We can also set up the filter later if preferred).

To do so, simply note the current query name or specifically set the name. I used the name TOC to remind me it is like a Table of Contents:

Since we want to exclude the Power Query results table (named TOC) from the table list in the future (even though it doesn’t appear right now), we can use the filter control on the Name column and select Text Filters > Does Not Equal and then enter the name (TOC in this case):

We can now Close & Load To a Table on the desired worksheet. Power Query sends this list of tables to a new table in the specificied location:

With this complete, it is time to add a helper column to display a reference to the location of each table within the workbook.

Create the reference column with CELL

We’ll create the Reference column using the CELL function. Excel’s CELL function returns info about the referenced cell.

So, in the cell immediately to the right of the Name column header, we type the desired name of our reference column (Ref) and the results table auto-expands to include it:

Since this results table is a table, any formula we enter into the first data cell in the new Ref column will be filled down automatically.

We enter the following formula:

=CELL("address",INDIRECT([@Name]))

When we hit Enter, bam:

Yay … we now have a column that shows the reference for the table, which includes the workbook name, the sheet name, and the upper-left cell.

A few notes at this point.

The first argument of the CELL funtion is the type of information we’d like about the cell. In this case, we asked for the “address” which includes the workbook, worksheet, and first cell in the range.

The second argument of the CELL function uses the INDIRECT function to convert the table name text string into an actual Excel reference.

The first result includes single quotes around the workbook and worksheet name, whereas the last two results don’t have them. This is because Excel automatically places them around names that include a space.

So, with that complete, our final step is to make it easy to go to each table with a hyperlink.

Create the link column with HYPERLINK

If we would like to make it easy for a user to navigate to a specific table in the list, we can add a Link column using the HYPERLINK function.

We create a new column in the results table by typing in the desired column name (Link) in the cell immediately to the right of our reference column header:

Then we enter the following formula:

=HYPERLINK("#"&[@Ref],"Go To")

The hyperlinks are created:

We can click the Go To link for the desired table, and Excel will navigate us there instantly.

A couple of notes about the HYPERLINK function.

The first argument of the HYPERLINK function provides the link destination. We prepend the hash/pound to indicate it is a bookmark to an internal reference.

The second argument is the friendly name that the user will see in the cell. I used Go To but you can use anything else you prefer.

Now, if we ever add a new table, change a table name, delete a table, or change a sheet name, we can simply right-click the results table and click Refresh.

Well, I think we accomplished our objective … yay!

If you enjoyed the post or have any suggestions, please share by posting a comment below … thanks!

Sample File