Vergel’s Excel and Visual Basic Blog

The INDIRECT() Function

March 11th, 2007

Given a cell reference in text, the INDIRECT() function returns the value in the referenced cell(s). For example, if A1 had the text “B1″ and B1 had the number 3, INDIRECT(”A1″) would return the number 3. The INDIRECT() function can be useful if you want a certain cell reference to never change no matter what. For example, if you put “=A100″ in a cell, and then delete any row before A100, the cell reference will automatically adjust to become “=A99″. But, if you use =INDIRECT(”A100″), it will not change.

The INDEX() function is also commonly used together with the ADDRESS() function. For example, a formula like “=INDIRECT(ADDRESS(ROW(NamedRange), COLUMN(NamedRange))” will return the value in the topmost and leftmost cell in the range named as “NamedRange”.

The syntax for the INDIRECT() function is:

INDIRECT(ref_text, A1)

Ref_text is a reference to a cell which can be in A1-style reference or R1C1-style reference. Ref_text can also be a defined name. INDIRECT returns #REF if ref_text is not a valid cell reference or if it refers to a cell in a closed workbook.

A1 is a logical value that specifies the reference style used. If it’s TRUE or omitted, A1-style reference is used. R1C1-style reference is used if A1 is FALSE.

Below are examples of how to use the INDIRECT() function. In the samples, Values is a named range that refers to A2:A8.

Download the sample workbook here .

Posted in Excel, Functions | No Comments