Welcome, my besties! Today, I’m going to share with you a mind-blowing tip. We’re going to use nothing but formulas to create a dynamic table of contents in Google Sheets. Imagine, a table of contents that automatically updates when you change the names of the sheets. It’s magical, isn’t it? So, let’s dive in!
Automatically Extracting Sheet Names
We start with a Google Sheet containing two sheets.
In Sheet2, we use the RAND function in cell A1:
=RAND()Then, in Sheet1, we put the following formula in cell A1:
=CELL("address", Sheet2!$A$1)This gives us the following result: Sheet2!$A$1
With a simple REGEX formula in Google Sheets, I can extract only the sheet name:
=REGEXEXTRACT(A1, "[^!]+")And voilà, the result shows only the sheet name: Sheet2
Now, pay close attention, here’s the next key step. I change the calculation setting of the workbook to update every minute.
File > Spreadsheet settings > Calculation > On change and every minuteNow, when I change the name of Sheet2 to something else (e.g., “Data”), the cell value in Sheet1 automatically updates within the next minute.
If you’re as geeky as I am, you’ll find this super cool. Everything happens automatically, without my intervention. I’ve used a formula to extract information outside the rows and columns of my sheet.
Internal Links in Google Sheets with the HYPERLINK Formula
Normally, you use the HYPERLINK function to create links to external websites or “mailto” links, essentially external links.
To create internal links within a Google Sheet, you click on the cell and create a special link.
It looks like a hyperlink, but it’s not based on a formula, so we can’t use it in conjunction with the CELL function. Plus, the link text doesn’t change.
However, there is a way to create internal links with the HYPERLINK function.
Each sheet (tab) of your Google Sheet has a unique #gid number added to the URL, indicated here in red:
https://docs.google.com/spreadsheets/d/1iX0jcBRxYfS_CYVHt2lxXnHswSdDOSpFz4HkL4/edit#gid=0Use this #gid number from the Google Sheets URL inside the HYPERLINK function.
Sheet1 (or the “first” sheet of your Google Sheet) has the number 0.
The other sheets have longer #gid numbers, for example, 349283867.
I copied this #gid number from Sheet2 into Sheet1, in cell B1, next to the CELL formula.
Then, in cell C1, I created this HYPERLINK formula:
=HYPERLINK("#gid=349283867", "Click here to open Sheet2")This creates a hyperlink that takes me to Sheet2 when I click on it.
Even better, if I change the name of Sheet2 to “Second Sheet” or “Dashboard” or “Ranking” or anything else, the link will automatically update, and I can still access that second sheet.
Create a Dynamic Table of Contents with Formulas
Let’s take this a step further. I used this technique to build a dynamic table of contents that always displays the correct sheet names.
It updates every minute or whenever a change is made.
Admittedly, it requires a lot of work for a result that’s not immensely useful, but it’s still an interesting and unusual application of programming with formulas.
The final step is to hide the two helper columns containing the REGEX formula and the #gid number, to give it the appearance of a table of contents.
Of course, this still requires a random number in a destination cell on each sheet, so that the REGEX function can reference it. And I have to set the calculation to “on change and every minute,” as discussed earlier.
(You can always hide the cells with the random numbers by formatting the text in white, for example.)
As always, I would love to hear your thoughts in the comments below!
Voilà, my friends, you now have a powerful tool in your hands. Feel free to try this trick in your own Google Sheets and explore further the possibilities of formulas.
If you want to discover more tips and tricks on Google Sheets, don’t hesitate to visit Crawlan.com, the website where I share my knowledge in digital marketing.
Have fun and see you soon for more discoveries on Google Sheets!
