Contextures - Microsoft
Excel and Access consulting

Search Contextures Sites

Excel INDIRECT Function

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

Excel
INDIRECT Function Arguments
Lock a Cell
Reference
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

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.

Download the sample
INDIRECT workbook

Excel INDIRECT Function Arguments

The Excel 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 Excel 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 Excel 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 Excel
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

cell A4.

To insert a row, right-click on the Row 4 button, and from the
pop-up menu, choose Insert.

As shown at right, cell B2, which uses the Excel 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.

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 Excel INDIRECT
function, to compare the A1 and R1C1 reference styles.

In cells A1:A5, type a set of numbers, as shown at right.

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 Excel INDIRECT function.

In this example, you'll create a formula with the Excel 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 at right.

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 Excel 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:

=IF(OR(A2="",B2=""),"",INDIRECT("'" &
A2 & "'!" & B2))

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 Excel 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
FTP site -- look for pull.zip:

ftp://members.aol.com/hrlngrv/

Laurent Longre has an addin (morefunc.xll) at:

ttp://xcell05.free.fr/

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 Excel 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:

Names --
Naming Ranges

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 Excel 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

Excel Functions Tutorials

Excel SUM
Function
Excel VLOOKUP
Function
Excel INDEX
function and Excel MATCH Function
Excel Count
Function
Excel INDIRECT
Function

Excel Video Tutorials

Excel
MATCH Function Video Tutorial
Excel
VLOOKUP Function Video Tutorial

Home     Excel
Tips     Excel
Files     The Excel
Store     Blog     Contact

Privacy Policy

Contextures Inc., Copyright 2011
All rights reserved.

Debra Dalgleish

Last updated: July 11, 2011 8:39 AM