Sums from different worksheets
 

Hi,

I've set up a workbook that contains a worksheet for each month (July 07,
August 07, etc). The work sheets are the same each month and contain
multiple dimensions of data: Day/Date, Week #, Shift, Process Type, Machine
#, Process Variable (Volume, Time, etc). I have named ranges appropriately.

example: | Day 1 | Day 2 |
Day 3 |
| Week 1
|

| Shft 1 | Shft 2 | Shft 1 | Shft 2
| Shft 1 | Shft 2 |

ProcessA - Machine A - Vol
- Time

- Machine B - Vol
- Time

ProcessB - etc, etc, etc

I have a summary page that uses a =sumproduct function with 4 critieria that
creates summaries for the month. For example this gives a Shift A, Volume
total for week 26 for Process A. This works well for 1 months worksheet.

=SUMPRODUCT((Shift=B$7)*(Process=$A$10)*(Parameter =$A12)*(WeekNum=B$3)*(DataRange))

Now I want to be able to automatically select the relevant Months
spreadsheet as another critiera - so I don't have to create new sumproduct
formulae for each month.

For instance if the Criteria matchs July 07, then the summary happens in
that months worksheet. When the criteria changes to August 07 - it will
create the summary from the August 07 worksheet, etc.

Is there anyway that anyone can think of to do this using functions?