Microsoft Office Tutorials and References

In Depth Information

**Using INDIRECT to Build and Evaluate Cell References on the Fly**

Using

Using
INDIRECT

INDIRECT
to Build and Evaluate Cell References on the Fly

to Build and Evaluate Cell References on the Fly

The INDIRECT function is deceivingly powerful. Consider this trivial ex-

ample: In cell A1, enter the text BB2. In cell B2, enter a number. In cell C3, enter

the formula =INDIRECT(A1)

=INDIRECT(A1). Excel returns the number that you entered in cell

B2 in cell C3. The INDIRECT function looks in cell A1 and expects to find

something that is a valid cell or range reference. It then looks in that ad-

dress to return the answer for the function.

The reference text can be any text that you can string together using various

text functions. This enables you to create complex references that dynamic-

ally point to other sheets or to other open workbooks.

The reference text can also be a range name. You could have a validation

list box where someone selects a value from a list. If you have predefined

a named range that corresponds to each possible entry on the list, INDIRECT

can point to the various named ranges on the fly.

When you use traditional formulas, even absolute formulas, there is a

chance that someone might insert rows or columns that will move the referen-

ce. If you need a formula to always point to cell J10, no matter how someone

rearranges the worksheet, you can use =INDIRECT("J10") to handle this.

Syntax

INDIRECT(ref_text,a1)

The INDIRECT function returns the reference specified by a text string. This

function takes the following arguments:

•
ref_text

ref_text
—
This is 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_textis not a valid cell ref-

erence, INDIRECT returns a #REF! error. If ref_textrefers to an ex-

ternal workbook, the other workbook must be open. If the source work-

book is not open, INDIRECT returns a #REF! error.

•
aa1
—
This is a logical value that specifies what type of reference is

contained in the cell ref_text. If a1is TRUE or omitted, ref_textis in-

terpreted as an A1-style reference. If a1 is FALSE, ref_textis inter-

preted as an R1C1-style reference.

Figure 12.40
is a monthly worksheet in a workbook that has 12 similar

sheets. In each worksheet, the data headings are in row 6, and the invoices ap-