Experiments With Cell Function: Create A Dynamic Table Of Contents In Google Sheets With Formulas

This post is an exploration of what’s possible with formulas alone.

I’m going to use formulas to create a dynamic table of contents that updates automatically when you change the sheet names.

The key to it all is the CELL function, which can extract the address of a cell.

Extracting Sheet Names Automatically

I start with a Google Sheet containing two sheets.

On Sheet2, I put the RAND function in cell A1:

Back in Sheet1, I put this formula into cell A1:

It gives me this output: Sheet2!$A$1

Using a simple Google Sheets REGEX formula, I can extract just the Sheet name:

This changes the output to just show the Sheet name: Sheet2

Now pay close attention, here’s the next key step. I switch the spreadsheet calculation to every minute.

File > Spreadsheet settings > Calculation > On change and every minute

Now, when I change the name of Sheet2 to something else (e.g. “Dataset”), the cell value in Sheet1 will change within the minute.

If you’re a nerd like me then that’s super cool.

It’s happening automatically, without me intervening.

I’ve used a formula to extract information that is outside the rows and columns of my actual Sheet.

Internal Google Sheet Links With The Hyperlink Formula

Normally you use the HYPERLINK function to link to external sites or for “mailto” links, basically external links.

To do internal linking within a Google Sheet, you click on the cell and create a special link.

It looks and feels like a hyperlink but it’s not formula driven, so can’t be used in conjunction with the CELL function technique above. Moreover, the text in the link doesn’t change.

However, there is a way to create internal links with the hyperlink function.

Each sheet (tab) within your Google Sheet has a unique #gid appended to the URL, shown here in red:

Use this #gid number from the Google Sheets’ URL inside the hyperlink function.

Sheet1 (or the “first” sheet in your Google Sheet) has the number 0.

Other sheets have longer #gid numbers, e.g. 349283867

I copied this #gid number for Sheet2 into Sheet1, in cell B1, next to the CELL formula.

Then in cell C1, I constructed this hyperlink formula:

This creates a working hyperlink that takes me to Sheet2 if I click on it.

Even better, if I change the name of Sheet2 to “Second Sheet” or “Dashboard” or “Leaderboard” or whatever, the link will automatically update and I can still click through to this second sheet.

This technique reminds me of the methods to create dynamic named ranges in Google Sheets. Use a side-effect of a regular function to do something interesting.

Create A Dynamic Table Of Contents With Formulas

Taking this a step further, I used this technique to build a live table of contents that always displays the correct sheet names.

It updates every minute or whenever a change is made.

Admittedly, it’s a lot of work for a not enormously useful result, but it’s still an interesting and unusual application of programming with formulas.

The last change is to hide the two helper columns containing the REGEX formula and the #gid number, to make it look more like a table of contents.

Of course, it still requires a random number in a destination cell on each sheet, for the REGEX function to reference. And I have to set the calculation to “on change and every minute”, as discussed above.

(You can always hide the random number cells by formatting the text white for example.)

As always, I’d love to hear your thoughts in the comments below!