Excel INDIRECT Function
The Microsoft Excel INDIRECT function returns a reference to a range, and I use it to make dependent drop down lists in my Excel workbook, to show items based on another cell's value. You can also use INDIRECT
to create a reference that won't change, if rows or columns are
inserted in the worksheet, where you are using the SUM function.
Author: Debra Dalgleish
Video: INDIRECT Function Examples
Watch this short Excel tutorial video, to see how to use the INDIRECT function on its own, or combined with other Excel functions.
Written instructions are below the video. Download the sample INDIRECT workbook, to see the examples, and to follow along with the video.
Video Timeline
 00:00 Introduction
 00:14 Lock a Cell Reference
 01:27 Refer to Named Range
 02:50 Refer To Specific Sheet
 04:22 Lock Array of Numbers
Thanks to Dave Peterson, for his contributions to this page.
How INDIRECT Function Works
The INDIRECT function is useful when you want to return a value, based on a text string. For example, select a range name from a drop down list, and get the total amount for the selected range.
In this screen shot, there is a drop down list in cell B2, where you can choose Actual or Budget. After you make a selection, the total for that type appears in cell B3.
INDIRECT Function Syntax Arguments
The INDIRECT function's syntax has two arguments: INDIRECT(ref_text,a1)
 ref_text: A cell reference or a text string (or both), that
create a range reference to a cell, range of cells or named range.
 a1: (optional) Logical value  does the reference use A1 reference
style?
 TRUE, or omitted  ref_text must be A1
style reference
 FALSE  ref_text must be R1C1 type of reference style

Warning: If the INDIRECT formula refers to a different workbook, that workbook must be open, or the formula will return a #REF! error. See examples on this page, for referring to a different worksheet, or a different workbook. 
Dependent Drop Down Lists

My favourite way to use the INDIRECT function is in data validation, to create dependent drop down lists. This technique makes it easier for people to find what they need, when entering data on an Excel worksheet. 
The short video below, shows the steps, and there are detailed written steps, and sample files, on the Dependent Drop Down Lists page.
For example,
 Select the Vegetable category in
the Product Type column
 The dependent drop down list in the Item column shows a list of
Vegetable options.
Data Validation Rule
The INDIRECT function is used in the data validation settings dialog box, like the formula example shown below
The video below shows how to set this up.
Video: Dependent Drop Down Lists
In this short video, you'll see how to set up a main drop down list, with a dependent drop down list in the next column, that uses the INDIRECT function.
There are written instructions, and a sample file to download, on the Dependent DropDown Lists Video page.
Intro to INDIRECT Video Examples
Here are the written steps for the INDIRECT examples that are in the Introduction to INDIRECT video, shown above.
1) Lock a Cell Reference
2) Refer to Different Sheet
3) Refer To Different Workbook
4) Refer to a Named Range
Lock a Cell Reference
If you create a SUM formula, and cells are inserted later, at the top of the list, the SUM formula might not include the new amounts. Here's how you can use the INDIRECT function to avoid that problem.
The INDIRECT function can "lock" a specific cell in a formula. Then, if rows or columns are inserted or deleted above or to the left of that cell, the reference does not change. Without INDIRECT, the reference would automatically adjust.
To see how this works, follow these steps to create two formulas  one
with normal cell references, and one with an INDIRECT
function combined with the SUM function.
 In cells C2:C7, type a list of numbers
 Copy the list into E2:E7
 In cell C8, type a SUM formula: =SUM(C2:C7)
 In cell E8, type this formula: =SUM(INDIRECT(“E2”):E7)
Insert a Row
To see the difference between the formulas, insert a blank row above row 2, and enter 100 for January, in cells C2 and E2.
 The total amount changes in column E, because the start cell is locked at E2.
 The total amount does NOT change in column C. The start cell shifts down to cell C3.
Refer To 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 exclamation mark, 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))
Refer to 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.
Set up a Data Workbook
 Create a new Excel file named TestFile.xlsx
 Change the first sheet name to Test Data
 On the Test Data sheet, enter numbers in cells A1:A10
 Save the workbook
Set up a Linked Workbook
Next, follow these steps, to create a workbook that will link to the data workbook. Or, get the download file, and go to the WkbkRef sheet.
 Create another new Excel file, named LinkedFile.xlsx
 On the first sheet, in cell A2, type the data file name, with or without file extension: Test File
 In cell A3, enter the sheet name: Test Data
 In cell A4, type A7  we'll pull a number from that cell in the data workbook
Get a Sample Formula
To see the syntax that you'll need to use in your INDIRECT formula, follow these steps:
 In the linked file, select
cell A7, and type an equal sign: =
 Switch to Test File workbook, click on cell A7, and press Enter
 In the formula bar, you'll see the reference that was created:
='[Test File.xlxs]Test Data'!$A$7
 To keep that formula visible, type an apostrophe in front of the equal sign
Create an INDIRECT formula
Next, follow these steps to create an INDIRECT formula that uses the same syntax. The formula will include
the single quote marks, square brackets and exclamation mark.
 In cell A6, type the following formula:
=INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)
How the Formula Works
 The first part of the string is a single quote and square bracket,
within a set of double quotes:
" ' [ " (I added spaces
here, so it's easier to read). Single quotes are included in the string to prevent errors if the
sheet name or file name contain space characters, like there are in this example
 The & (ampersand) characters join all the parts of the text string together.
 A2 is the cell that has the data file name
 Next, there are characters to end the data workbook name  a square bracket, within a set of double quotes: " ] " (spaces
added for clarity)
 A3 is the cell with the sheet name
 Next, there are characters to end the data sheet name  a single quote and exclamation mark, within a set of double quotes: " ' ! " (spaces
added for clarity)
 A4 is the cell with the
data cell address.
Test the Formula
 Change the cell address in cell A4, and the result in A6 will change.
 After the INDIRECT formula is working, you can delete the sample link in cell A5
Formula Notes
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.xlsx workbook is closed, the
INDIRECT 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:
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.
Ref_Text Argument Examples

This technical section isn't required reading  it shows examples for the ref_text argument in the INDIRECT function. Keep reading if you'd like to learn more about these options 
In most cases, a cell reference or combination of text string and cell reference works best with the INDIRECT function.
Text string  address is typed into the formula (not flexible)
 A1 style: =INDIRECT("A4") or =INDIRECT("Demo!B3") or =INDIRECT("Budget")
 R1C1 style: =INDIRECT("R4C1",FALSE)
 Note: If you use a simple text string, the range is "hard coded" in the formula, which has limited usefulness, except for locking a cell reference.
Cell reference  refers to a cell that contains a text string
 A1 style cell reference: =INDIRECT(C6)
 R1C1 reference style: =INDIRECT(C8, FALSE)
Combined  Text string and Cell reference
 A1 style: =SUM(INDIRECT("A4:A" & C12))
Get the Sample File
 INDIRECT Examples (for Video): Get the sample INDIRECT workbook that has examples used in the INDIREXT Examples video.
The zipped file is in xlsx format, and does not contain any macros.
Symbols

Excel Tip 

Technical Tip 

Warning 
Related Pages
Functions List
INDIRECT  Compare Sheets
VLOOKUP
CHOOSE
COUNT / COUNTIF
INDEX / MATCH