Google Sheets INDIRECT: Dynamic Cell References & Tab Switching
INDIRECT converts a text string into an active cell or range reference. Enables dynamic switching between sheet tabs (e.g. querying Jan, Feb, Mar depending on a dropdown selection) without re-writing formulas.
=INDIRECT(cell_reference_as_string)Technical Overview & Engine Mechanics
Formula Comparison: INDIRECT vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| cell_reference_as_string | Text | Required | String representing cell or range (e.g. "B5" or "'Q1'!A1:D"). |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Dynamic Tab Lookup Based on Dropdown Selection
=SUM(INDIRECT("'" & A1 & "'!B2:B100"))Example 2: Preserve Cell Coordinate During Row Insertion
=INDIRECT("A1")Example 3: Dynamic Summary Table Across 12 Monthly Tabs
=ARRAYFORMULA(INDIRECT("'" & A2:A13 & "'!Z1"))Example 4: Dynamic Sheet Tab Selection from Dropdown
=SUM(INDIRECT("'" & B1 & "'!D2:D100"))Example 5: Dynamic Column Range Builder
=AVERAGE(INDIRECT("Data!" & C1 & "2:" & C1 & "500"))Common Errors & Solutions
Performance Pro Tips
- INDIRECT is a volatile function that recalculates on every user edit. Use sparingly on 50,000+ row workbooks to prevent calculation latency.
Frequently Asked Questions: INDIRECT
Why do sheet tabs with spaces cause #REF! in INDIRECT?
Tab names with spaces must be enclosed in single quotes: "'" & A1 & "'!A1:B10".
What does 'volatile function' mean for INDIRECT?
Volatile functions re-evaluate on every user action in the workbook, consuming CPU even when their inputs haven't changed.
Does INDIRECT work with external spreadsheets?
No. INDIRECT only references ranges inside the active workbook. Use IMPORTRANGE for external files.
Can INDIRECT be used with VLOOKUP?
Yes: =VLOOKUP(key, INDIRECT("'" & TabName & "'!A:D"), 2, FALSE).
Can I reference named ranges with INDIRECT?
Yes: =SUM(INDIRECT("Q4_Sales")) dynamically evaluates the named range.
Related Google Sheets Formulas
XLOOKUP
XLOOKUP is the modern, flexible replacement for VLOOKUP and HLOOKUP. It searches left, right, up, and down, defaults to exact match, handles missing data gracefully, and can return entire rows or columns without breaking when columns are inserted.
Lookup & ReferenceIMPORTRANGE
IMPORTRANGE imports a specified cell range from another external Google Spreadsheet workbook. It provides real-time multi-sheet linking with built-in OAuth access permission controls.
Lookup & ReferenceINDEX / MATCH
Combining INDEX with MATCH provides two-way horizontal and vertical matrix lookups. It looks left and right, supports dynamic column and row coordinates, and is resilient against structural spreadsheet changes.