Formula to Sum a definable range of workbooks
 

I have created a workbook that collects daily data on 31 worksheets (1 per
day) and I am now trying to create a summary sheet at the end.

I am trying to create a formula that will allow a user to enter a start date
and end date which will provide a sum of all the work completed between the
given range. Somthing along the lines of :

sum ('$A$1:$B$1'!C1)

Where $A$1 and $B$1 refer to two cells which contain the name of the first
and last worksheet in the range (the worksheets are called
'1','2','3'...'31') and C1 is the cell to be calculated.

Thanks in advance for your help.

Matt Chattaway

One possible way would be to put all sheet names in a column/row aqnd then
use a workaround, assume you put 1 - 31 in Z1 - Z31 (you need to creat a
whole list of the sheet names in use), I used column Z because it is off the
normal display of a spreadsheet and you can even use a nother sheet and hide
that sheet if you want, assume we use Z1:Z31, you still use A1 and B1 for the
sheets involved, then this formula will work


=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET($Z$1,$A$1-1,,$B$1-$A$1+1)&"'!C1"),"<"""))



Regards,

Peo Sjoblom

"mr_chattaway" wrote:

I have created a workbook that collects daily data on 31 worksheets (1 per
day) and I am now trying to create a summary sheet at the end.

I am trying to create a formula that will allow a user to enter a start date
and end date which will provide a sum of all the work completed between the
given range. Somthing along the lines of :

sum ('$A$1:$B$1'!C1)

Where $A$1 and $B$1 refer to two cells which contain the name of the first
and last worksheet in the range (the worksheets are called
'1','2','3'...'31') and C1 is the cell to be calculated.

Thanks in advance for your help.

Matt Chattaway