Use Indirect To Get A Data From A Multi-cell Range
February 15, 2021 - by Bill Jelen

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 specified 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 sufficiently 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"))

Summary: You can use INDIRECT
to grab data from a multi-cell range.
Title Photo: Boba Jaglicic at Unsplash.com
This article is an excerpt from Excel Gurus Gone Wild.