The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.

**Thanks to Dave Peterson, for his contributions to this page.**

The INDIRECT function has two arguments:

**ref_text**: A cell reference or text string (or both), that create the range reference. The referenced range can be a cell, a range of cells, or a named range.**a1**: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE, or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style.

Note: If the INDIRECT formula refers to a different workbook, that workbook must be open, or the formula will return a #REF! error.

If you create a simple link to a cell, e.g. **=A4**, then insert
a row above row 4, your formula will automatically change to **=A5**.
To prevent this change, you can use the INDIRECT function.

To demonstrate the difference, you'll create two formulas -- one that uses a simple link, and one that uses a text string with an INDIRECT function.

- In cell A4, type a number (10 in this example).
- In cell B2, type the following formula:

**=INDIRECT("A4")** - Press the Enter key, and the formula will return the number in cell A4.
- In cell C2, type the following formula:

**=A4** - Press the Enter key, and the formula will return the number in cell A4.
- To insert a row, right-click on the Row 4 button, and from the
pop-up menu, choose
*Insert*. - As shown below, cell B2, which uses the INDIRECT function, now
returns 0, because cell A4 is empty. The text string,
**"A4"**, in the INDIRECT formula did not change when the row was inserted. - Cell C2, which contains the link, still returns 10, because its
formula has changed to
**=A5**.

To see the steps for creating a formula with the INDIRECT function, please watch this video tutorial.

To create the ref_text argument, you can also refer to a cell, and use its contents. This makes the formula more flexible, as its results will change, if the cell contents change.

In this example, you'll create formulas with the INDIRECT function, to compare the A1 and R1C1 reference styles.

- In cells A1:A5, type a set of numbers, as shown below.
- In cell C2, type:
**A4** - In cell C2, type:
**R4C1** - In cell D2, type the formula:
**=INDIRECT(C2)** - Press the Enter key, and the formula returns the number in cell A4
- In cell D3, type the formula:
**=INDIRECT(C3,FALSE)** - The FALSE in the second argument indicates that the R1C1 reference style will be used.
- Press the Enter key, and the formula returns the number in cell A4

To create the ref_text argument, you can combine text strings and cell references within an INDIRECT function.

In this example, you'll create a formula with the INDIRECT function, using a number entered in a cell, and a text string with the column letter.

- In cells A1:A5, type a set of numbers, as shown below.
- In cell C2, type the number 4.
- In cell D2, type the formula:
**=INDIRECT("A"&C2)** - Press the Enter key, and the formula returns the number in cell A4
- Change the number in cell C2, and the result in D2 will change.

Note: If cell C2 is empty, the formula will return an error. To prevent this, you can add an IF function:

=IF(C2="",0,INDIRECT("A"&C2))

An INDIRECT formula can also refer to cells on other worksheets. In this example, you'll create a formula with the INDIRECT function, using references to a sheet name and cell name.

- On a worksheet named Data Sheet, enter numbers in cells A1:A10
- On a different sheet, in cell A2, enter the sheet name:
**Data Sheet** - In cell B2, type a cell name from the range of numbers, e.g.:
**A2** - In cell C2, type the following formula:

**=INDIRECT("'" & A2 & "'!" & B2)** - The first part of the string is a single quote within a set of
double quotes:

**" ' "**(spaces were added for clarity)

- After the reference to A2, which contains the sheet name, is a
single quote and apostrophe, within a set of double quotes:

**" ' ! "**(spaces were added for clarity) - The string ends with a reference to cell B2, which contains the cell address.
- Single quotes are included in the string to prevent errors if
the sheet name contains space characters.

- Press the Enter key, and the formula returns the number in cell A2 on the Data Sheet worksheet.
- Change the number in cell C2, and the result in D2 will change.

Note: If either cell A2 or B2 is empty, the formula will return an error. To prevent this, you can add an IF function:

An INDIRECT formula can refer to cells in other workbooks, but **will
return a #REF! error if that workbook is closed**. In this example,
you'll create a formula with the INDIRECT function, using references
to a file name, sheet name and cell name.

- In an open file named TestFile.xls, on a worksheet named Test Data, enter numbers in cells A1:A10
- On a sheet in a different workbook, in cell A2, enter the file
name:

Test File.xls - In cell A3, enter the sheet name:
**Test Data** - In cell A4, type a cell name from the range of numbers, e.g.:
**A7** - To see the syntax that you'll need to use in your formula, select
cell A7, and type an equal sign:
**=** - Switch to Test File.xls, and click on cell A7
- In the formula bar, you'll see the reference that was created:

**='[Test File.xls]Test Data'!$A$7** - You'll create an INDIRECT formula that uses the same syntax, adding the single quote marks, square brackets and apostrophe.
- In cell A6, type the following formula:

**=INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)** - The first part of the string is a single quote and square bracket,
within a set of double quotes:

**" ' [ "**(spaces were added for clarity)

- After the reference to
**A2**, which contains the file name, is a square bracket, within a set of double quotes:

**" ] "**(spaces were added for clarity) - After the reference to
**A3**, which contains the sheet name, is a single quote and apostrophe, within a set of double quotes:

**" ' ! "**(spaces were added for clarity) - The string ends with a reference to cell
**A4**, which contains the cell address. - Single quotes are included in the string to prevent errors if
the sheet name contains space characters.

- Press the Enter key, and the formula returns the number in cell A7 on the Test Data worksheet, in the Test File.xls workbook.
- Change the values in cells A2:A5, and the result in A6 will change.
- You can delete the sample link in cell A5

Note1: If A2, A3 or A4 is empty, the formula will return an error. To prevent this, you can add an IF function:

=IF(OR(A2="",A3="",A4=""),"",INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4))

**Note2**: If the Test File.xls workbook is closed, the
above formula will return a #REF error. I haven't used the following
files, but they may help you if you need to pull data from a closed
workbook:

- Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's download site:
- Laurent Longre has an addin (morefunc.xll) at:
- It includes =indirect.ext() that may help you.

In addition to cell references, you can refer to named ranges in an INDIRECT formula. In this example, the INDIRECT function is used to sum the selected named range.

- In cells A1:B5, type headings and numbers, as shown at right.
- Name cells A2:A5 as East, and cells B2:B5 as West. There are naming instructions here:
- In cell D2, type the name of one of the ranges, e.g.
**East** - In cell E2, type the formula:
**=SUM(INDIRECT(D2))** - Press the Enter key, and the formula returns the sum of numbers in the East range.
- Change cell D2 to West, and the formula returns the sum of numbers in the West range.

The INDIRECT function can be used with Data Validation, to create dependent dropdown lists. For example, select the Fruit category in column A, and the data validation list in column B shows a list of Fruit options.

There are instructions for this technique here:

Data Validation - Dependent Lists

Download the sample INDIRECT workbook

Custom Search

Last updated: April 8, 2015 7:27 PM