Lookup & Reference FormulaGoogle Sheets V8 Engine Verified

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.

Formula Syntax:=INDIRECT(cell_reference_as_string)

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
cell_reference_as_stringTextRequiredString 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"))
How it works: Sums Column B of whichever sheet tab name is selected in dropdown cell A1.

Example 2: Preserve Cell Coordinate During Row Insertion

=INDIRECT("A1")
How it works: Always references cell A1 even if new rows are inserted above it.

Example 3: Dynamic Summary Table Across 12 Monthly Tabs

=ARRAYFORMULA(INDIRECT("'" & A2:A13 & "'!Z1"))
How it works: Pulls total cell Z1 across 12 monthly tabs listed in Column A.

Common Errors & Solutions

❌ #REF! Function INDIRECT parameter 1 is not a valid cell reference
Resolution: If sheet tab names contain spaces, wrap them in single quotes: `'Tab Name'!A1`.

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

Does INDIRECT recalculate automatically when data changes?

Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.

Is this formula compatible with Microsoft Excel?

Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.

Related Google Sheets Formulas