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.

Formula Table Of Contents

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:

=RAND()

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

=CELL("address",Sheet2!A1)

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

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

=REGEXEXTRACT(CELL("address",Sheet2!A1),"'?([^']+)'?!")

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:

https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit#gid=77003961

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:

=HYPERLINK("#gid="&B1,A1)

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.

Experiments with cell function

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!

7 thoughts on “Experiments With Cell Function: Create A Dynamic Table Of Contents In Google Sheets With Formulas”

  1. Hi Ben,
    I’d just recently signed up to your free advanced formulas course, (which is excellent I might add). I mention this as I’m new to google sheets and had to post on Googles doc forum to get help with a formula with a hyperlink within a sheet. There someone also mentioned your name (in a positive way I might add), so that’s how I found you,,,, (I know you really wanted to know all that!) 🙂

    I’ve just read the above, and now read that when hyperlinking within the same workbbook, I can now do this just by referencing ‘#gid’ instead of the full doc URL!
    Brilliant stuff!
    You are a God! 🙂
    Keep up the great work Ben.
    Best regards
    John C

  2. Hi Ben. I have individual SOURCE files that I link to a separate overview ‘Aggregator’ file. In the SOURCE file TEMPLATE there is a hyperlink in a cell named “File”. This is IMPORTRANGE into Aggregator view as a quick way of jumping to the SOURCE file. Is there a quick way of puling the spreadsheet URL in to each new SOURCE file created from the TEMPLATE, or does it need to be done manually each time.

  3. I started working with Excel in ca. 1995, simple macros, etc… I switched to Google Sheets 4 years ago, so I’m quite an avid spreadsheet user for 25 years, uninterrupted.

    Ben, your site is the very best in this topic – IMHO by far. Very useful examples, cristal clear, but not too lenghty explanations. Congrats and a big thank you!

  4. So admittingly, my initial read was very cursory and I had skimmed due to time constraints. The very first formula ended up tripping me up, and I had to double-back and re-read. Just to confirm if I’m interpreting this accurately, is the purpose of the `rand()` function purely as a means to autonomously trigger a continuous poll as to update to the sheet from server-side with no local/user/other actions/dependencies?

    1. Yes! That’s it. Since the Sheet is set to recalculate every minute, I need something that will automatically update and force a refresh of the CELL function.

      Cheers,
      Ben

Leave a Reply

Your email address will not be published. Required fields are marked *