Microsoft Office Tutorials and References
In Depth Information
Using INDIRECT to Build and Evaluate Cell References on the Fly
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.
The INDIRECT function returns the reference specified by a text string. This
function takes the following arguments:
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-