weekly table from daily data

Posted by Joseph Cowan on December 28, 2001 4:18 AM

I may not have been clear,
I have a sheet in a workbook.
The workbook is Metric Update Data Entry.xls
The sheet is Member
Column A, beginning at cell A5 is 01/01/01
A6 is 01/02/01, etc.
B5 contains data, as does b6, etc

01/01/01 2567
01/02/01 3789

I have another workbook, weekly summary.xls
I have a sheet Member
In Column A I have weeks as A5 01/06/01, A6 01/13/01, etc
Data (summing say B11:B17) would be in B column

01/06/01 18765
01/13/01 20156

I want to feed the weekly summary worksheet from the Metric Update Data Entry worksheet without having to enter cell by cell, using autofeed. Can this be done?
Please contact me by email if I am missing the obvious here.

Thanks

Posted by Ian Mac on December 28, 2001 5:22 AM

First I'd put the start of the week in column A and the end in B. unless you can get away with putting the start of the week in instead of the end (I think that's how I've read it).

with the first way put this in column c:

=SUMPRODUCT((A5:A47>=A5)*(A5:A47&LT;=B5)*(B5:B47))

you can adjust the ranges to suit your needs, i.e. the ranges A5:A47 would be replaced with reference to your other workbook (I'd also consider having them in the same workbook and use a summary sheet??)

the second way (If you can't get away with the start and finish dates (although you can easily hide the first column) would be:

=SUMPRODUCT((A5:A46>=(A1-7))*(A5:A46&LT;=F1)*(B5:B46))

but with this i've noticed you've use the 6th day of the week i.e. Saturday?? but that won't make any difference, if this is the case just replace the -7 with -6.

Any help??

Ian Mac

Posted by Ian Mac on December 28, 2001 5:23 AM

Typo!!

The second one should be:

=SUMPRODUCT((A5:A46>=(A5-7))*(A5:A46&LT;=A5)*(B5:B46))

Posted by Joseph Cowan on December 28, 2001 7:42 AM

Re: Typo!!

I must be VERY dull today.
I can't see how you would reference the data cells if you use A & B for dates using the first formula, I ca't get the program to recognize the name of the worksheet either.
Could we address this via email?
jcowan@express-scripts.com
I need the seperate worksheets as I have a ton of data, I am building a display (small footprint) worksheet for the data in a huge spreadsheet that I want to auto load using vlookups on a daily basis (that's finished) and now I want to pull the daily data into a second display that will present the data as weekly summaries on a Sunday to Saturday week, here we use ending Saturday as the week indicator.