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
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
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.