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?
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, 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.
Comments
Post a Comment