Formula (or VBA Macro) to add cells over dynamic Worksheets

Hi Everyone,

I am normally pretty good about researching and finding answers on my own, but I am on a time crunch here and can't figure this one out.

I have a Workbook that will contain a variable number of worksheets labeled 1-# (where # can be between 28-31) for the days in a given month.

I need to develop either a formula or macro that will add the numerical values in specific cells across a user defined list of those worksheets.

For example, Let us assume that the cell I wish to have summed is A1 and the user wants to see the summation of worksheets 5, 6, 7, 8, and 9.

I have a cell in a different worksheet that allows the user to select the first date/worksheet (5 in this case) and one that allows the user to select the last date/worksheet (9 in this case). These are cells E6 and E7 respectivley. I know that I can use =INDIRECT("'"&E6&"'!A1") to return the value in the cell A1 of the first worksheet (5!A1) and I know that I can use a 3-D formula to sum A1 accross multiple worksheets: =SUM(5:9!A1).

What I cant figure out is how to 'nest' these two formulas together. I have tried:

=SUM(INDIRECT("'"&E6&":"&E7&"'!A1)) and
=SUM(INDIRECT("'"&E6:E7&"'!A1)) and
=SUM(INDIRECT("'"&E6&:&E7&"'!A1))

I have also tried =SUM(INDIRECT('E6:E7'!A1)) which returns a #REF! error and it (curriously) changes the formula to =SUM(INDIRECT('E6:[E7]E7'!A1)). I have no idea why it is adding the [E7] into this formula.

Any help would be great.

You could try this

Formula:

Please Login or Register  to view this content.

where E1 and E2 hold the first and last sheets.

Originally Posted by Bob Phillips

You could try this

Formula:

Please Login or Register  to view this content.

where E1 and E2 hold the first and last sheets.

This worked perfectly!

Would it be too much to ask you to explain how/why this works? That way I have the understanding of the work and not just the temporary fix?

Mainly I am not 100% sure why there are two "indirect" functions and why it uses sumproduct. Also, I dont know why row is used at all.

I think that the SUMIF and the "<>" argument tells it only to sum cells that are not blank, and the second indirect appears to be similar to what I was trying to do; but the rest has me stumped.

Anyway, thanks again!