Search Contextures Sites

NOTE: Special Introductory Offer

-50% offFastExcel V3 Bundle Price

use Coupon CodeFXLV3Intro

Offer valid until July 31 2014

Excel INDIRECT Function

Thanks to Dave Peterson, for his contributions to this page.INDIRECT Function Arguments

Lock a Cell Reference

Video: INDIRECT Function

Create a Reference from a Cell Value

Create a Reference from a Cell Value and Text

Create a Reference to a Different Sheet

Create a Reference to a Different Workbook

Refer to a Named Range

Dependent Data Validation Lists

Download the Sample File

More Function Tutorials

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.## INDIRECT Function Arguments

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.## Lock a Cell Reference

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.## Video: INDIRECT Function

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

## Create a Reference from a Cell Value

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
## Create a Reference from a Cell Value and Text

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))## Create a Reference To a Different Sheet

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:## Create a Reference To a Different Workbook

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.
## Refer to a Named Range

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.
## Dependent Data Validation Lists

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 File

Download the sample INDIRECT workbook

## Functions Tutorials

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Function

INDIRECT FunctionNOTE: Special Introductory Offer

-50% offFastExcel V3 Bundle Price

use Coupon CodeFXLV3Intro

Offer valid until July 31 2014

Contextures Inc., Copyright ©2014

All rights reserved.

Last updated: June 9, 2014 10:51 PM