ref_text - Required. A text string representing a cell reference (e.g., "A1", "Sheet2!B5", or cell containing text reference)
a1 - Optional. Optional. TRUE (or omitted) = A1 reference style, FALSE = R1C1 reference style. Most users use A1 style.
| A | B | C | |
|---|---|---|---|
| 1 | Cell Ref | Value | Result |
| 2 | B5 | ||
| 3 | |||
| 4 | |||
| 5 | 1000 | ||
| 6 | Formula: | =INDIRECT(A1) 1000 |
Convert text "A1" to cell reference
=INDIRECT("A1") → value from A1
A1="Feb" returns value from Feb!B10
=INDIRECT("Feb!B10") → Feb sheet data
B1="A10" creates range A1:A10
=SUM(INDIRECT("A1:A10")) → sum range
A1="2024" sums named range Sales2024
=SUM(INDIRECT("Sales2024")) → total
You have monthly revenue sheets (Jan, Feb, Mar through Dec) and want a summary dashboard that pulls data from a specific month based on dropdown selection. The INDIRECT function in Excel enables dynamic sheet references that automatically switch data sources when users change selections. This Excel INDIRECT formula is essential for financial consolidation reports, multi-location dashboards, department performance tracking, and any scenario where you need to aggregate data from multiple similarly-structured sheets without creating 12 separate formulas.
| A | B | C | |
|---|---|---|---|
| 1 | Selected Month: | Feb | |
| 2 | Revenue: | ||
| 3 | Formula: | =INDIRECT(A1&"!B10") $45,200 |
Sum values from column A through a variable ending column stored in a cell. The Excel INDIRECT function builds dynamic ranges that adjust based on user input or formula logic. Perfect for flexible budget reports where the number of months changes monthly, dynamic pivot table alternatives, quarterly vs annual reporting where column counts vary, and automated report generation that adapts to changing data structures without manual formula updates.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | End Col: | D | |||
| 2 | Values: | 100 | 200 | 150 | 250 |
| 3 | Sum Formula: | =SUM(INDIRECT("A2:"&A1&"2")) 700 |
❌ The Problem:
✅ Solution:
=INDIRECT("B5")The INDIRECT function in Excel requires text strings. Either hardcode "B5" in quotes or ensure the cell contains text (not formula). If A1 shows B5, it must be entered as text, not as a formula. Use INDIRECT("B5") or store "B5" as text in A1, then INDIRECT(A1).
❌ The Problem:
✅ Solution:
=[Workbook.xlsx]Sheet1!A1Use direct external reference syntax for closed workbooks. The Excel INDIRECT formula is volatile and requires the source workbook to be open. For cross-file references that work when files are closed, use standard bracket notation without INDIRECT. INDIRECT is best for dynamic references within the same workbook.
❌ The Problem:
✅ Solution:
=INDIRECT(A1&"!"&B1)Always include the exclamation mark separator when building cross-sheet references with INDIRECT. Format must be SheetName!CellAddress. The Excel INDIRECT function expects valid reference syntax. Use A1&"!"&B1 to construct proper sheet references dynamically for multi-sheet formulas.
Master these INDIRECT Function variants:
From basics to advanced - AI generates perfect formulas instantly.