The INDIRECT function returns a reference specified by a text string.
It allows you to create dynamic cell references that change automatically based on text values.
Syntax
=INDIRECT(ref_text, [a1])
Arguments:
- ref_text → A text string representing a cell reference
- a1 (optional) → TRUE (A1 style) or FALSE (R1C1 style)
Basic Example
If:
Cell A1 = 100
Formula:
=INDIRECT("A1")
Result → 100
Even though “A1” is written as text, INDIRECT converts it into a real cell reference.
Dynamic Reference Example
If:
Cell A1 = B1
Cell B1 = 500
Formula:
=INDIRECT(A1)
Result → 500
Because A1 contains text “B1”, INDIRECT returns value from B1.
Using INDIRECT with Sheet Names
You can reference another sheet dynamically.
Example:
=INDIRECT("Sheet2!A1")
Returns value from cell A1 in Sheet2.
Dynamic Sheet Reference Example
If:
Cell A1 = Sheet2
Formula:
=INDIRECT(A1 & "!A1")
This returns value from A1 of the sheet written in cell A1.
Practical Uses
- Dynamic dropdown reports
- Switch between sheets automatically
- Create flexible dashboards
- Dependent data validation lists
INDIRECT with Data Validation
You can create dependent dropdown lists using named ranges and INDIRECT.
Example:
If dropdown 1 = “IT”
INDIRECT will reference a named range called IT.
Important Notes
- INDIRECT does not work with closed workbooks
- It is a volatile function (recalculates often)
- Incorrect text reference returns #REF! error
- Sheet names with spaces require single quotes
Example:
=INDIRECT("'Sales Report'!A1")
INDIRECT vs OFFSET
| INDIRECT | OFFSET |
|---|---|
| Uses text reference | Uses row/column movement |
| Good for dynamic sheet reference | Good for dynamic ranges |
| Converts text to reference | Moves from starting cell |
Best Practices
- Ensure text reference is correct
- Use named ranges for better clarity
- Avoid excessive use in large files
- Double-check sheet names
Conclusion
The INDIRECT function is a powerful tool for creating dynamic and flexible references in Excel. It is especially useful in dashboards, dynamic reports, and advanced data validation setups.