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.

Search JabSto ::

Custom Search