The INDIRECT
worksheet function is a useful tool for creating cell or range references
"on the fly", as a formula is evaluated, rather than "hard
coding" them into the formula. Microsoft's on-line Help documentation
of this function only hints at is usefulness.
In its simplest usage, the
INDIRECT function allows you
to put the address of one cell in another, and get data from the the first cell
by referencing the second. For example, if cell A1
has the value "C3", then =INDIRECT(A1)
will return the value in
C3.
The real power of the INDIRECT
function is that it can turn any string into a reference. This includes any
string that you build up using string constants and the values of other cells in
the formula, strung together with the &
concatenation operator. For example, the simple formula
=SUM(A5:A10)
will sum the values in the range A5:A10.
However, suppose you want to be able to specify which range of rows to sum
"on the fly", without having to change the formula. The INDIRECT
function allows you to do this. Suppose you put your starting row cell B1,
and your ending row in C1. Then, you can
use the formula
=SUM(INDIRECT("A"&B1&":A"&C1))
The argument to the INDIRECT function
is
"A"&B1&":A"&C1
If B1 contains 5 and C1
contains 10, this evaluates to the string "A5:A10".
The INDIRECT function converts
this string to an actual range reference, which is passed to the SUM
function.For VBA formulas to return relative sheet names (e.g., the name of
the next or previous worksheet), see the Referencing
Worksheets From Formulas page.
Another useful feature of the
INDIRECT function is that since it takes string
argument, you can use it to work with cell references that you don't want Excel
to automatically change when you insert or delete rows. Normally,
Excel will change cell references when you insert or delete rows or columns,
even when you use absolute referencing. If you have
the formula =SUM($A$1:$A$10), and then
insert a row at row 5, Excel will convert the formula to =SUM($A$1:$A$11).
If you don't want this to happen, use the INDIRECT function
to change a text string to a reference:
=SUM(INDIRECT("A1:A10"))
Since Excel sees "A1:A10" as a text
string rather than a range reference, it will not change it when rows or columns
are deleted or inserted.
This feature is important when working with some array
formulas. Frequently, an array formula will use the ROW()
function to return an array of numbers. For example, the following formula
will return the average of the 10 largest numbers in the range A1:A60
:
=AVERAGE(LARGE(A1:A60,ROW(1:10)))
However, if you insert a row between rows 1 and 10, Excel will change the
formula to
=AVERAGE(LARGE(A1:A60,ROW(1:11)))
which will return the average of the 11 largest numbers. If we use
the function with a string, Excel won't change the reference, so the
formula will remain correct, regardless of whether and where rows are inserted
or deleted.
=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))