CASE STUDY 8
Google Sheets - Dynamic Dashboards
Dynamic Dashboard over Multiple Sheets
How to create a Dynamic Dashboard over Multiple Sheets
Google Sheets is used by many in building all kinds of tables, easy or complex calculations for business but also for personal use.
Sometimes, we use one Google Sheets file with several sheets. When these sheets are getting too many, it can be a problem navigating through them, leave alone getting a friendly, comprehensive dashboard of all the data inside. And to make it more complex, if you keep adding new sheets in your master file, even though you already have a dashboard, how do you update the data to incorporate the new sheets?
Here comes in handy to mix several essential functions in some more complex formulas.
Let's suppose that you are organizing various events. They all follow a typical pattern where you collect your guests' data like names, contact details, and maybe you need to generate some unique tokens for their invites. Now, each event has its tab or sheet. You keep adding tabs (sheets) with each event, but then you might want to have an overview of all the events you organize, how many guests are expected, and maybe even any missing information you could still be looking for.
In the following part, we will see how you can:
- create a dynamic list of all your sheets for the file
- create formulas that will automatically update your overview tab, your dashboard, with every new event that you create on its tab
- and some more tips on how to improve this
Make sure you are registered to see the answer.
Registered members have FULL ACCESS to the content. REGISTERING IS FREE!
In this file, the main functions used are: IF, COUNTA, COUNTUNIQUE, CONCATENATE (or “&”) and INDIRECT
But first is first: get a list of all your Sheets (tabs) from the file:
Google Script
- Don’t worry, on you have to do is Copy / Paste 😉
- On your Google Sheet page, go to Tools / Script Editor and once this opens in a new window, delete all the content inside and paste the following:
- This creates a custom function called SheetNames() and this function will give you the names of all the sheets in the file
- Click the save button (the floppy disk icon to the left of Run) and your script is ready to be used. Sometimes, you must first hit the Run button first so that Google Script can run once and ensure that the code is properly written and the correct access rights are given to the script.
Google Script – Use the newly created formula
- In our example, on cell A9, by entering the formula =sheetNames(), this will add all the names of the sheets on that column.
Include the names of the sheets in the formulas
=COUNTUNIQUE(INDIRECT(“‘”&A10&”‘!C2:C”))
- In this case we want to count the number of countries found in column C of the Events data from each sheet.
- For line 10, this should read something like =COUNTUNIQUE(‘Event 1 – Coffee Talk’!C2:C)
- How can we replace the ‘Event 1 – Coffee Talk’!C2:C with a reference to the information in Column A? See the bold data here “‘“&A10&”‘!C2:C“? That’s exactly what we are looking for as an expression and we use the & symbol to link all these together.
- Now, we need to trick Google Sheets a bit and tell him that in fact, this needs to be used as a reference, not a normal text so we add all to the INDIRECT function: INDIRECT(“‘”&A10&”‘!C2:C”)
- We apply the COUNTUNIQUE function to the formula we created above et voila … COUNTUNIQUE(INDIRECT(“‘”&A10&”‘!C2:C”))
This content is locked
Login To Unlock The Content!
