Microsoft Office Tutorials and References

In Depth Information

**USE INDIRECT TO GET A DATA FROM A MULTI-CELL RANGE**

Gotcha:
Formulas built with
INDIRECT
are particularly susceptible to

generating
#REF!
errors if someone changes a worksheet name. Say that you

have the
=3!B4
. If you change the name of the worksheet from 3 to Sep 3, the

formula automatically changes to
=‘Sep 3’!B4
. However, when you start

using
INDIRECT
, the formula fails when someone changes the name of the

worksheet. If you use
INDIRECT
, you need to convince people not to change

the worksheet names or protect the workbook.

Summary:
A date used in
INDIRECT
always changes back to the date serial

number. You need to use other functions to force the date to appear in the

proper format.

USE INDIRECT TO GET A DATA FROM A

MULTI-CELL RANGE

Challenge:
As described in several other topics,
INDIRECT
is pretty cool for

grabbing a value from a cell. Can
INDIRECT
point to a multi-cell range and be

used in a
VLOOKUP
or
SUMIF
function?

Solution:
You can build an
INDIRECT
function that points to a range. The

range might be used as the lookup table in a
VLOOKUP
or as a range in
SUMIF

or
COUNTIF
.

In Figure 36, the formula pulls data from the worksheets speciﬁ ed in row 4.

The second argument in the
SUMIF
function looks for records that match a

certain date from column A.

Note:
Because each worksheet might have a different number of records, I

chose to have each range extend to 300. This is a number that is sufﬁ ciently

larger than the number of transactions on any sheet. The formula in cell B5

is:

=SUMIF(INDIRECT(B$4&"!A2:A300"),$A5,INDIRECT(B$4&"!C2:C300"))

Figure 36.
Each
INDIRECT
points to a rectangular range on the other worksheet.

Summary:
You can use
INDIRECT
to grab data from a multi-cell range.