Quick Answer

INDIRECT Function

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

Syntax

INDIRECT

(ref_text, [a1])

Parameters

Parameter	Description	Required
ref_text	A text string that describes a cell reference or named range.	Required
a1	If 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).