INDIRECT

Syntax:

INDIRECT ( ref-text [ , [ A1-ref-style-flag ] ] )

Description: Locates the reference specified by ref-text and evaluates that reference to get to its underlying value.
Note: : This function should be used when the reference to a cell within a formula is to be changed without changing the formula itself.

Arguments:

Name

Type

Description

ref-text

An A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a string.

If ref-text refers to another workbook (i.e., it's an external reference), that other workbook shall be open.

A1-ref-style-flag

logical

Specifies the kind of reference that is contained in the cell ref-text. If TRUE or omitted, ref-text is interpreted as an A1-style reference (§3.17.2.3.1); otherwise, ref-text is interpreted as an R1C1-style reference (§3.17.2.3.2).

Return Type and Value: any – The underlying value of the location referred to by ref-text.

However, if

  • ref-text is not a valid cell reference, #REF! is returned.

  • ref-text refers to another workbook yet that other workbook is not currently open, the return value is unspecified.

example:

A

B

1

Data

Data

2

B2

1.333

3

B3

45

4

George

10

5

5

62

where A2 contains a reference to B2, A3 contains a reference to B3, A4 contains the defined name George that refers to B4, and A5 contains the row number of B5:

INDIRECT($A$2) results in 1.333INDIRECT($A$3) results in 45INDIRECT($A$4) results in 10INDIRECT("B"&$A$5) results in 62

INDIRECT("R[-1]C",FALSE) uses the cell in the previous row and current column.]