INDIRECT function limitations

[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?

 

[Harlan Grove]

The Indirect function does not seem to work for a range of
worksheets. . . .

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

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?

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.

 

[Peo Sjoblom]

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)),"<

"))

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

 

[Brian]

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

 

[Harlan Grove]

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

....

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.