Summing across multiple sheets using a changeable reference cell

[ajnmx]

I've spent a couple of hours searching this group but I can't seem to
find a solution that works!

I have a workbook with sheets named with dates, but let's call them
"A", "B", "C", "D" etc

I have a 'from' date in cell A1 and a 'to' date in cell A2, so let's
say these are 'B' and 'D'

So what I want is a formula that will sum cell F8 on each of the
sheets in the range from 'B' to 'D'

I know using INDIRECT and SUM doesn't work, but from reading this
group I think there's probably some way of using SUMPRODUCT but I just
can't get anything to work. Any help greatly appreciated!

 

[Harlan Grove]

I have a workbook with sheets named with dates, but let's call them
"A", "B", "C", "D" etc

I have a 'from' date in cell A1 and a 'to' date in cell A2, so let's
say these are 'B' and 'D'

So what I want is a formula that will sum cell F8 on each of the
sheets in the range from 'B' to 'D'

I know using INDIRECT and SUM doesn't work, but from reading this
group I think there's probably some way of using SUMPRODUCT but I just
can't get anything to work. Any help greatly appreciated!

This isn't consistent with your subject.

Start by creating an *ORDERED* list of the worksheet names in a range.
I'll use WSLST to refer to this list. It should be in order.

The expression

N(INDIRECT("'"&WSLST&"'!F8"))

will return an array of the F8 values in each of these worksheets
(it'll return 0 for any cell evaluating to text, blank or FALSE, and 1
for any cell evaluating to TRUE). Use this in a formula like the
following to sum over a specific range of worksheets.

=SUMPRODUCT((WSLST>=From)*(WSLST<=To),N(INDIRECT("'"&WSLST&"'!F8")))

If you want a method for generating a list of worksheet names
automatically, see

http://groups.google.com/group/micr...5/c996ac93fb63b01e?lnk=st&q=#c996ac93fb63b01e

or

http://tinyurl.com/5h5o4t

 

[ajnmx]

That is genius! Works perfectly. Thank you sooooo much (and sorry
about the subject!).