Google Sheets•3 min read
Google Sheets INDIRECT: Build Dynamic Sheet References
Reference dynamic tab names, ranges, and multi-sheet summaries in Google Sheets using the powerful INDIRECT function.
SheetHub••3 min
Hardcoding tab names in formulas makes multi-month workbooks and regional reports rigid and difficult to scale. If your workbook has twelve monthly tabs (
The
Imagine a workbook containing separate tabs for four regions:
You can nest
When someone switches the dropdown from
To look up an employee's salary in column B based on their ID in column A from whichever department tab is selected in
To structure clean, self-documenting multi-step formulas without nested string repetitions, consider organizing your calculations using Google Sheets LET formulas.
The
Jan, Feb, Mar, etc.), writing separate formulas for every single sheet leads to repetitive formula editing. Google Sheets INDIRECT solves this challenge, converting text strings into live cell and range references dynamically.
This tutorial explains how to use =INDIRECT() in Google Sheets, build dropdown-driven summary dashboards, handle sheet names with spaces, and avoid calculation slowdowns.
What is Google Sheets INDIRECT and how does it work?
INDIRECT function takes a string of text and evaluates it as an actual cell or range address.
Syntax
=INDIRECT(cell_reference_as_string, [is_a1_notation])- cell_reference_as_string: A text string representing a valid cell or range (e.g.,
"A1","Summary!B5", or"'" & A1 & "'!B2:B10"). - is_a1_notation (optional):
TRUEfor standard A1 notation (default),FALSEfor R1C1 notation.
Pattern 1: Dynamic tab selection using a dropdown
North, South, East, and West. Each tab stores total revenue in cell B10.
In your summary dashboard:
- Put a dropdown list in cell
G2containing the region names (North,South, etc.). - In cell
H2, enter the following formula to fetch revenue dynamically:
=INDIRECT("'" & G2 & "'!B10")Why single quotes are essential:
Wrapping the sheet name in single quotes (') ensures the formula works even if tab names contain spaces or special characters (such as 'Q1 Sales'!B10).
Pattern 2: Dynamic ranges inside aggregation formulas
INDIRECT inside any standard calculation function like SUM, AVERAGE, or VLOOKUP.
To calculate the sum of Column C from the selected tab:
=SUM(INDIRECT("'" & G2 & "'!C2:C100"))North to West, SUM immediately recalculates against the West tab's data range.
For dashboards that combine multi-sheet metrics with structured SQL-like aggregations, pair this with our Google Sheets QUERY function complete guide.
Pattern 3: Dynamic lookup across variable sheets
G2:
=VLOOKUP(F2, INDIRECT("'" & G2 & "'!A2:D50"), 2, FALSE)Troubleshooting and best practices
| Error / Pitfall | Cause | Solution |
|---|---|---|
#REF! (Cannot find range) | The tab name in the text string does not match any existing sheet. | Ensure exact spelling and wrap tab references in single quotes ('). |
| Workbook lag | INDIRECT is a volatile function that recalculates on every edit. | Use bounded ranges (e.g., A1:B100 instead of A:B) to preserve sheet performance. |
| Incompatible formula moves | Copy-pasting text strings does not automatically shift relative addresses. | Use ADDRESS or ROW if dynamic row shifting is required. |
Summary
INDIRECT function is the foundation of flexible Google Sheets dashboards. By turning text strings into active references, you can build consolidated reports that adapt instantly to user selections.Article Topics
Recommended Next Reading
Google Sheets
=GOOGLE(...)Google Sheets UNIQUE Function: Extract Distinct Values
Explore ↗
Google Sheets
=GOOGLE(...)Google Sheets COUNTUNIQUEIFS: Count Unique by Criteria
Explore ↗
Google Sheets
=EDATE() / =TODAY()Google Sheets SEQUENCE: Generate Numbers, Dates & Grids
Explore ↗
Share this tutorial
Discussion & Community
Share questions, tips, or edge-cases about this spreadsheet formula.