ajnmx wrote...
....
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/micro...96ac93fb63b01e
or
http://tinyurl.com/5h5o4t