fx
FunctionPedia
Quick Answer

INDIRECT Function

Turns text that looks like a cell reference into an actual reference.

✓ Excel✓ Google SheetsExcel All versions

Syntax

INDIRECT
(ref_text, [a1])

Parameters

ParameterDescriptionRequired
ref_textA text string that describes a cell reference or named range.Required
a1If TRUE, ref_text is interpreted in A1 style. If FALSE, R1C1 style. Default is TRUE.Optional

Basic Example

Reference a cell from text

=INDIRECT("A1")
ResultValue in A1

The text "A1" is converted to the cell reference A1.

Advanced Examples

Example 1: Dynamic sheet reference

Multi-sheet summary

Pull a value from a sheet named in cell B2

=INDIRECT(B2 & "!A1")
Result: Value from sheet A1
If B2 contains "Sheet2", the formula references Sheet2!A1.

Example 2: Dynamic named range

Dashboard

Sum a named range selected from a dropdown

=SUM(INDIRECT(A2))
Result: Sum of selected named range
A2 contains a named range name, and INDIRECT converts it to a reference for SUM.

How INDIRECT Works

INDIRECT takes a text string and evaluates it as a cell reference. This allows dynamic references based on formulas or user input. However, the resulting reference is not updated when cells move or insert.

1
Build reference text
Create a string that looks like a cell reference, e.g. "A1" or "Sheet2!B2".
2
Wrap with INDIRECT
Type =INDIRECT(ref_text).
3
Use the reference
Use it inside other functions like SUM, VLOOKUP, etc.

Important Notes & Limitations

  • Volatile: recalculates whenever the workbook changes, which can slow down large workbooks.

  • Does not update if rows/columns are inserted or deleted.

  • Cannot reference closed workbooks.

  • A1 parameter defaults to TRUE (A1 style).

Common Errors & Fixes

#REF! errorThe text string is not a valid reference or the sheet does not exist.

Fix: Verify the text string and sheet names.

Sheet name errorSheet names with spaces need single quotes.

Fix: Use 'Sheet Name'!A1 in the text string.

Circular referenceThe text string refers to the cell containing the formula.

Fix: Restructure the formula to avoid self-reference.

Download Practice File

Practice INDIRECT with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the INDIRECT function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Tutorials

Absolute vs Relative Reference

Absolute vs relative reference in Excel explained: what $ really locks, when to use mixed refs, the F4 shortcut, and dynamic alternatives in Google Sheets.

Excel REF! Error Fix

The complete Excel REF error fix: why #REF! appears, which edits break cell references, and how to repair the damage or prevent it from ever returning.

Google Sheets IMPORTRANGE

Google Sheets IMPORTRANGE pulls live data from another spreadsheet into yours. Learn the syntax, how to grant access, and how to fix the #REF! access error.

INDIRECT Function: Build Dynamic References From Text

INDIRECT function dynamic reference: convert text into a live reference for dynamic sheet names, expandable SUM ranges and dependent dropdowns in Excel.

VLOOKUP From Another Sheet

VLOOKUP from another sheet just needs a sheet prefix. Learn the cross-sheet syntax, quoted sheet names, INDIRECT for dynamic tabs and 3D references in Excel.

Frequently Asked Questions

Is INDIRECT volatile?
Yes, it recalculates on every change, which can slow performance.
Can INDIRECT reference another workbook?
Only if the workbook is open. Closed workbooks return #REF!.
How do I use INDIRECT with data validation?
Create a dropdown that selects a named range, then use INDIRECT to pull that range.