What INDIRECT Does
The INDIRECT function takes a text string that represents a cell reference and converts it into an actual reference that Google Sheets can evaluate. This lets you build references dynamically — changing which cell, range, or even sheet a formula points to based on the contents of other cells. It is the key to creating flexible dashboards, dynamic data validation dropdowns, and formulas that adapt based on user input.
Syntax
=INDIRECT(cell_reference_as_string, [is_A1_notation])
| Parameter | Description |
|---|---|
| cell_reference_as_string | A text string representing a cell or range reference (e.g., "A1", "Sheet2!B5", or a cell containing such a string). |
| is_A1_notation | Optional. TRUE (default) for A1-style references, FALSE for R1C1-style. |
Basic Examples
Reference a cell dynamically
| A | B | |
|---|---|---|
| 1 | B3 | |
| 2 | ||
| 3 | 500 |
=INDIRECT(A1)
Result: 500
A1 contains the text "B3", and INDIRECT converts it into a live reference to cell B3, which holds 500.
Pull data from a user-selected sheet
Suppose you have sheets named "Q1", "Q2", "Q3", and "Q4", each with revenue in cell B2. A summary sheet lets the user type a quarter name:
| A | B | |
|---|---|---|
| 1 | Quarter | Revenue |
| 2 | Q2 | =INDIRECT(A2 & "!B2") |
Result in B2: The value from cell B2 on the "Q2" sheet.
Changing A2 to "Q3" instantly pulls from the Q3 sheet instead.
Sum a dynamic range
| A | B | |
|---|---|---|
| 1 | Start Row | 2 |
| 2 | End Row | 10 |
=SUM(INDIRECT("C" & B1 & ":C" & B2))
Result: The sum of C2:C10. Changing B1 or B2 adjusts the range on the fly.
Advanced Examples
Create a dynamic data validation dropdown
You can use INDIRECT to make a dependent dropdown — where the choices in one dropdown depend on the selection in another. If you have named ranges "Fruit" (Apple, Banana, Mango) and "Vegetables" (Carrot, Pea, Spinach):
- Cell A1 has a dropdown with "Fruit" and "Vegetables".
- Cell B1 uses data validation with the source
=INDIRECT(A1).
When the user picks "Fruit" in A1, B1 shows Apple, Banana, and Mango. Picking "Vegetables" switches B1's options to Carrot, Pea, and Spinach. This pattern is widely used in forms and data entry sheets.
Reference a named range dynamically
If you have named ranges like "Sales_2024" and "Sales_2025", and cell A1 contains the year:
=SUM(INDIRECT("Sales_" & A1))
Typing 2025 in A1 makes the formula sum the "Sales_2025" range. This avoids duplicating formulas for each year and makes the sheet more maintainable.
Common Mistakes
- Getting a #REF! error from mistyped references. INDIRECT does not validate the string before evaluating it. If the text does not form a valid reference (e.g., a typo like
"Shet1!A1"instead of"Sheet1!A1"), you get an error. Wrap with IFERROR for safety. - Forgetting to quote sheet names with spaces. If your sheet is named "Sales Data", the reference string must include single quotes:
=INDIRECT("'Sales Data'!A1"). Without them, INDIRECT cannot parse the reference. - Performance issues with large-scale use. INDIRECT is volatile — it recalculates every time the spreadsheet changes, even if the referenced data has not. Using hundreds of INDIRECT formulas can slow down your sheet noticeably. Consider INDEX/MATCH as a non-volatile alternative where possible.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.