I need to create a spreadsheet that will drop information over 4 weeks old.
Each sheet is 1 day and totals are cumulative up to 4 weeks. For example
fred has 1 in 1st week, 1 in 2nd week (2 total) 1 in third (3 total), 1 in
4th (4 total) and 1 in 5th week (4 total because week 1 drops off)
--
yardcow

Hi

Rather than using separate sheets for each day,I would be inclined to keep
the data all on one sheet, so on Sheet1 in A1:A3 put the headers
Name Date Value
fred 1/11/2005 1
jim 2/11/2005 1
fred 8/11/2005 1
etc...

Then on Sheet2 in cell A1 put Name, B1 Startdate C1 End Date D1 Value

In D2 enter
=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A2),--(Sheet1!$B1:$B$1000=$B$1),--(Sheet1!$B$2:$B$1000<=$C$1),Sheet1$C$2:$C$1000)
Copy D2 down through D3:D20 or for as many people as you are monitoring.

Then if you enter your names down column A of Sheet2, from A2 to the extent
of your list of names, then enter in B1 of sheet2 the first date of your 4
week period, and in cell C1 of sheet2 your ending 4 week date, you will have
the totals for each person for the relevant 4 week period.
Each time you step the date up by 1 week in C1 and D1 to get the next 4 week
period.

Regards

Roger Govier


yardcow wrote:
I need to create a spreadsheet that will drop information over 4 weeks old.
Each sheet is 1 day and totals are cumulative up to 4 weeks. For example
fred has 1 in 1st week, 1 in 2nd week (2 total) 1 in third (3 total), 1 in
4th (4 total) and 1 in 5th week (4 total because week 1 drops off)