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
USE INDIRECT TO GET A DATA FROM A
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
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.