INDIRECT function limitations

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

The Indirect function does not seem to work for a range of
worksheets. For example, Sum(Jan!:Aug!A4) works for
summing all the A4's on the worksheets between Jan! and
Aug!. But lets say A1 contains "Jan!" and A2
contains "Aug!", INDIRECT(A1&":"&A2&"A4") does not work.
Is they a workaround?
 
Brian said:
The Indirect function does not seem to work for a range of
worksheets. . . .
Click to expand...

Correct. INDIRECT can only return _Range_ references, and 3D references
aren't ranges. This may seem picky, but software is always picky.
. . . For example, Sum(Jan!:Aug!A4) works for
summing all the A4's on the worksheets between Jan! and
Aug!. . . .
Click to expand...

No, it doesn't. =SUM('Jan!:Aug!'!A4) might work, but worksheet names
containing exclamation points must be given inside single quotes.
. . . But lets say A1 contains "Jan!" and A2
contains "Aug!", INDIRECT(A1&":"&A2&"A4") does not work.
Is they a workaround?
Click to expand...

You'll need a list of worksheet names. Assuming you had defined the name LST
referring to ={"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug"}, then try

=SUMPRODUCT(N(INDIRECT("'"&LST&"'!A4")))

Using N() means that this only works for a single cell over multiple
worksheets. An alternative would be

=SUMPRODUCT(SUMIF(INDIRECT("'"&LST&"'!A1:B1"),"<>"))

Excel's 3D functionality remains (mired) in a class by itself.
 
There is a workaround, if the names are month abbreviations, just put
the sheet names in a list, let's say R1:R12 (put Jan in R1, copy down to R12
with Dec)

Now you can use this formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET($R$1,MATCH($A$1,$R$1:$R$12,0)-1,,MATCH
($A$2,$R$1:$R$12,0)-MATCH($A$1,$R$1:$R$12,0)+1,)&"'!"&CELL("address",A4)),"<
"))
Click to expand...

there might be better ways but this will work
where A1 holds the first sheet name and A2 the last
and R1:R12 holds the names of the sheets
 
Thanks Peo,

That formula was perfect. I understand that the Offset
and indirect portion of the formula return a reference to
some sheet and some cell (ex. Jan!A4), but why does the
combination of SumProduct and Sum If make this formula
work when there doesn't seem to be any reference to a
range?

Thanks for your help. That's one doozy of a formula.

Brian
 
Brian said:
That formula was perfect. I understand that the Offset
and indirect portion of the formula return a reference to
some sheet and some cell (ex. Jan!A4), but why does the
combination of SumProduct and Sum If make this formula
work when there doesn't seem to be any reference to a
range? ....
....
Click to expand...
....

There are ranges. INDIRECT returns only ranges. The argument to INDIRECT is
an array of text references (range addresses as text), and the result from
INDIRECT is an array of range references (an undocumented feature). Either
intentionally or not, SUMIF can deal with arrays of range references, and it
returns an array of the conditional sums from each range as an array of
numbers. The final SUMPRODUCT sums that array.
 
Back
Top