Excel Dynamic SUM function

[Patrick1327]

Hi,

I am hoping that someone can help me!

I have an Excel workbook that contains some daily production data for a given month.

The first worksheet is a summary for the month and is named "Summary".

Also in the workbook are worksheets named "1" for the first day of the month, "2" for the second, etc.

On the summary sheet, I would like to retreive the value of the cell "E19" for each "daily worksheet".

For example:

A1 A2 A3
4/1/04 4/2/04 =SUM('1:2'!E19)

I would like the SUM formula in cell A3 on the summary sheet to dynamically change the '1:2' in the SUM formula to reflect the days listed in A1 and A2 respectively.

I tried to do something like:

=SUM("'"&T(DAY(A1))&":"&(T(DAY(A2))&"'!E19"))

and

=SUM("'"&DAY(A1)&":"&DAY(A2)&"'!E19")

Any suggestions would be helpful.

Thanks in advance!

 

[bluedragon2]

Yes, I suggest you put all the data into one sheet. This way you can run your quries and such much easier and more dynamic. You will be able to benefit from pivot table information as well.

Even if you have you data on one sheet, you can still have your monthly sheets, but do all of your calculations from tha master sheet.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GlennUK]

You could have 2 dummy sheets called "Start" and "End", and use a formula of =SUM(Start:End!E19), and drag the positions of Start and End sheets to where you want to have them.

Glenn.

 

[Bowers74]

Is Cell E19 empty for the days where there is no data available (i.e. Worksheets 23-30, cells E19 for this months's Workbook are empty?)
[tt]
1. Goto Insert->Name->Define...
2. Enter a name (i.e. YTDSUM)
3. Clear the Reference field
4. Select Worksheet 1
5. Press and hold the Shift Key and Select the
Worksheet for the last day of the month.
6. Select Cell E19
7. Click OK
[/tt]
Now you only need to enter the formula =SUM(YTDSUM) into the cell where you want to see the sum for all of the E19 cells (i.e. Year to Date Sum).

Personally, I would put ALL of the production data for the ENTIRE YEAR into one worksheet and use SUMPRODUCT to get my summaries on a seperate sheet, but you wanted it this way!

I hope this helps!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884