Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings.

Description

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax

INDIRECT(ref_text, [a1])

The INDIRECT function syntax has the following arguments:

• Ref_text    Required. A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.
  1. If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
     
     Note    External references are not supported in Excel Web App.
  2. If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
     
     Note    This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.
• A1    Optional. A logical value that specifies what type of reference is contained in the cell ref_text.
  • If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
  • If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
B2	1.333
B3	45
George	10
5	62
Formula	Description	Result
‘=INDIRECT(A2)	Value of the reference in cell A2. The reference is to cell B2, which contains the value 1.333.	1.333
‘=INDIRECT(A3)	Value of the reference in cell A3. The reference is to cell B3, which contains the value 45.	45
‘=INDIRECT(A4)	Because cell B4 has the defined name “George,” the reference to that defined name is to cell B4, which contains the value 10.	10
‘=INDIRECT(“B”&A5)	Combines “B” with the value in A5, which is 5. This, in turn, refers to cell B5, which contains the value 62.	62

Cell Reference

Use the INDIRECT function in Excel to convert a text string into a valid cell reference.

1. For example, take a look at the INDIRECT function below.

Explanation: =INDIRECT(A1) reduces to =INDIRECT(“D1”). The INDIRECT function converts the text string “D1” into a valid cell reference. In other words, =INDIRECT(“D1”) reduces to =D1.

2. The simple INDIRECT function below produces the exact same result.

3. Do we really need the INDIRECT function? Yes. Without using the INDIRECT function, this would be the result.

4. Use the & operator to join the string “D” with the value in cell A1.

Explanation: the formula above reduces to =INDIRECT(“D1”). Again, =INDIRECT(“D1”) reduces to =D1.

Range Reference

Use the INDIRECT function in Excel to convert a text string into a valid range reference. For example, combine the SUM function and the INDIRECT function.

Explanation: the formula above reduces to =SUM(INDIRECT(“D3:D6”)). The INDIRECT function converts the text string “D3:D6” into a valid range reference. In other words, =SUM(INDIRECT(“D3:D6”)) reduces to =SUM(D3:D6).

Named Range

Use the INDIRECT function in Excel to convert a text string into a valid named range.

1. For example, the AVERAGE function below uses the named range Scores.

Explanation: the named range Scores refers to the range D1:D3.

2. However, the AVERAGE function below returns an error.

Explanation: =AVERAGE(“Scores”) returns an error because Excel cannot calculate the average of a text string!

3. The INDIRECT function below does the trick.

Explanation: =AVERAGE(INDIRECT(“Scores”)) reduces to =AVERAGE(Scores).

Worksheet Reference

Use the INDIRECT function in Excel to create a dynamic worksheet reference.

1. This is what a simple worksheet reference looks like.

Note: cell A1 on Sheet1 contains the value 10. Cell A1 on Sheet2 contains the value 20. Cell A1 on Sheet3 contains the value 30.

2. On the Summary sheet, enter the INDIRECT function shown below. Use the & operator to join the sheet name in cell A1 with “!A1”.

Explanation: the formula above reduces to =INDIRECT(“Sheet1!A1”). The INDIRECT function converts the text string “Sheet1!A1” into a valid worksheet reference. In other words, =INDIRECT(“Sheet1!A1”) reduces to =Sheet1!A1.

3. If your sheet names contain spaces or other special characters, enclose the sheet name in single quotation marks. Modify the INDIRECT function as shown below.