INDIRECT

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax

INDIRECT(ref_text,a1)

Ref_text   is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

A1   is a logical value that specifies what type of reference is contained in the cell ref_text.

• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Remarks

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Examples

If cell A1 contains the text "B2", and cell B2 contains the value 1.333, then:

INDIRECT($A$1) equals 1.333

If you change the text in A1 to "C5", and cell C5 contains the value 45, then:

INDIRECT($A$1) equals 45

If the workspace is set to display R1C1-style references, cell R1C1 contains R2C2, and cell R2C2 contains the value 1.333, then:

INT(INDIRECT(R1C1,FALSE)) equals 1

If B3 contains the text "George", and a cell defined as George contains the value 10, then:

INDIRECT($B$3) equals 10

When you create a formula that refers to a cell, the reference to the cell will be updated if the cell is moved by using the Cut command to delete the cell or if the cell is moved because rows or columns are inserted or deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax:

INDIRECT("A10")