If you build financial dashboards or project management trackers in Google Sheets, you likely have a massive workbook filled with a dozen different tabs (e.g., “January”, “February”, “March”). If you want to pull the Total Revenue number (cell C10) from the January tab onto a master dashboard, you simply type =January!C10.
However, what if you want to create a dropdown menu on your dashboard where a user can select a month, and the dashboard automatically updates to pull data from that specific tab? You cannot just type =A1!C10 (where A1 is the dropdown menu), because Google Sheets will literally try to find a tab named “A1”.
To solve this problem, you must use the INDIRECT function. INDIRECT takes raw text strings and forcibly converts them into actual, working cell references.
The Syntax of INDIRECT
The syntax is: =INDIRECT("cell_reference_as_text")
If you type =INDIRECT("B5"), Google Sheets doesn’t output the word “B5”. It actively goes to cell B5, retrieves whatever data is inside it, and displays it.
Step 1: Set Up the Dropdown Menu
Let’s build the dynamic dashboard.
- Create three tabs at the bottom of your sheet named exactly: January, February, and March.
- In each of those tabs, type a random sales number into cell C10 (e.g., 500, 800, 1200).
- Create a fourth tab named Dashboard.
- On the Dashboard tab, click cell A1. Go to Insert > Dropdown. Type in the options: January, February, and March.
Step 2: Write the Dynamic INDIRECT Formula
Now, we want cell B1 on the Dashboard to display the C10 value from whichever month is selected in the A1 dropdown.
- Click on cell B1 on the Dashboard.
- Paste the following formula:
=INDIRECT(A1 & "!C10") - Press Enter.
How the Formula Works
This is where the magic happens.
A1refers to the dropdown menu. Let’s say the user selected “February”.- The
&symbol is the concatenation operator. It glues text together. "!C10"is just a dumb text string representing the cell you want to target on the other tab.
Inside the formula, Google Sheets glues “February” and “!C10” together to create the text string "February!C10". The INDIRECT function then takes that dumb text string, instantly recognizes it as a valid location within the workbook, and fetches the data from the February tab.
Crucial Tip: Dealing with Spaces in Tab Names
If your tabs have spaces in their names (e.g., “Jan Sales” instead of just “January”), standard spreadsheet syntax requires you to wrap the tab name in single quotes (e.g., 'Jan Sales'!C10). If you don’t include those quotes, the INDIRECT formula will instantly break and throw an error.
To make your formula completely bulletproof against spaces, you must manually hardcode those single quotes into the concatenation string like this:
=INDIRECT("'" & A1 & "'!C10")Now, regardless of whether the user selects “January” or “January Sales Data 2025” from the dropdown, the formula will correctly wrap the text in quotes and fetch the data perfectly.