INDIRECT

Excel Functions

Create a reference from text

Syntax

EXCEL

=INDIRECT(ref_text, [a1])

Arguments

Parameter	Description
`ref_text`	A reference supplied as text.
`a1`	[optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.

Return Value

A valid worksheet reference.

Details

The INDIRECT function converts a text string like "Sheet1!A1" into a valid reference like =Sheet1!A1. That sounds simple enough, but of all Excel's many functions, INDIRECT might be the most confusing to users. Why would you use text when you can simply provide a normal reference? Well, one reason is that you already have a reference as text (perhaps in a cell), and you want to make Excel understand the text as a reference. Another reason is that you want to build a dynamic reference using different bits of information. With text, it's easy to hardcode some values, pick up other values on the worksheet, and join the values together using concatenation. The problem, however, is that once you have created a reference as text, Excel won't recognize it as a reference. To Excel, it's just an or

Examples

Quick syntax demo

INDIRECT takes two arguments, in a generic syntax like this:

EXCEL

=INDIRECT(ref_text,[a1])

Quick syntax demo

Ref_text is the text string to evaluate as a reference. The second argument, a1, is optional and indicates the "style" of the reference provided. When

EXCEL

=INDIRECT("A1") // returns a reference to A1
=INDIRECT("C5") // returns a reference to C5
=INDIRECT("R1C1",FALSE) // returns a reference to A1
=INDIRECT("R5C3",FALSE) // returns a reference to C5

Example 1 - the basic idea of INDIRECT

This happens because SUM doesn't see the text value as a reference; it simply sees a text string:

EXCEL

=SUM(E6)
=SUM("C5:C6")
=0

Example 1 - the basic idea of INDIRECT

Notice in the second line below, we still have a text value, but in the third line we have the range C5:C6, and SUM now returns 9:

EXCEL

=SUM(INDIRECT(E6))
=SUM(INDIRECT("C5:C6"))
=SUM(C5:C6)
=9

Example 2 - Variable worksheet name

In the example shown below, INDIRECT is set up to use a variable sheet name. The formula in cell C5 is:

EXCEL

=INDIRECT(B5&"!A1") // sheet name in B5 is variable

Example 2 - Variable worksheet name

If the sheet names in your worksheet include spaces or punctuation, use the formula below:

EXCEL

=INDIRECT("'"&B5&"'!A1") // single quotes added