I would like to display daily data, accumulated from a 12 month calendar on a
SUMMARY sheet at the end.
This data is hours worked in the "last" 30 days, 90 days and 365 days,
stored in cells AN10:AP40 for each month.
Looking for direction ...
Thanks

Wayne McK.

One way to set it up ..

Assuming the layout below
is typical for each of the 12 monthly sheets
in AN10:AO40 whe
AN10:AN40 contains actual dates, and
AO10:AO40 contains hours worked values

01-Jan-04 10
02-Jan-04 10
03-Jan-04 10
04-Jan-04 10
etc

and the 12 monthly sheets are named simply as: 1, 2, 3 ... 12

Then you could try setting up in a new sheet: Summary
the table below:

DaysPast 1 2 3 ... 12
365
90
30

where B1:M1 contains the 12 sheetnames: 1,2,3, ... 12
and in A2 down is listed the desired "last" x days: 365, 90, 30 ..

To populate the table,

Put in B2:

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,'1'!$AO$10:$AO$40)

Copy across to M2, fill down to A4

The above will return the required summations
from the range AO10:AO40
in each of the 12 monthly sheets
where the dates in AN10:AN40
fall within the last x days as specified in col A

To complete the table, you could just have a "Total" in N1,
put in N2: =SUM(B2:M2)
copy down to N4

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Wayne" wrote:

I would like to display daily data, accumulated from a 12 month calendar on a
SUMMARY sheet at the end.
This data is hours worked in the "last" 30 days, 90 days and 365 days,
stored in cells AN10:AP40 for each month.
Looking for direction ...
Thanks

Wayne McK.

Apologies, correction to formula:

Put in B2:

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,'1'!$AO$10:$AO$40)

should instead be:

Put in B2:

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,INDIRECT("'"&B$1&"'!$AO$10:$AO$40"))

(corrected the last part of the formula, forgot the INDIRECT(...) ..)

Note: In the preceding suggestion, the range AP10:AP40 was ignored. If you
also have "hours worked" in that range to be conditionally summed in the same
manner, just make a duplicate of the sheet: Summary, and change the range to
sum to point to "AP10:AP40" instead,

i.e. Put in B2 (in the duplicated sheet):

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,INDIRECT("'"&B$1&"'!$AP$10:$AP$40"))

Then fill across and down to populate the table as before

This will return the corresponding results for the range "AP10:AP40"

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max .. Tks for the help. I should have provided better info to start with.
AN10:AP40 contain hours worked
AQ10:AQ40 contain days of the month
AN9 contains 30 days
AO9 contains 90 days
AP9 contains 365 days (12 months)
AQ9 contains days of the month

sheets are noted, Jan, Feb, Mar ... 2005 Summary

Year "2005 Summary" Sheet, has numerous accumulated records for the past yr.
The 30, 90, 365 day column's have a column header =NOW()
k3 contains =Now()
K4 contains 30 days
L4 contains 90 days
M4 contains 365 days

By year, A20 contains the current Year, and row 20 has many year SUM totals.
I would like to indicated the =Now() 30, 90, 365 days hours worked in:
K3 contains =NOW()
K4 30 day
L4 90 day
M4 365 day

K20 ? =NOW() hours worked
L20 ? =NOW() hours worked
M20 ? =NOW() hours worked

It would really make my day to solve this!

Again, Merci, Thanks for the help!

Wayne ... Montreal Canada
"Max" wrote:

Apologies, correction to formula:

Put in B2:

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,'1'!$AO$10:$AO$40)

should instead be:

Put in B2:

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,INDIRECT("'"&B$1&"'!$AO$10:$AO$40"))

(corrected the last part of the formula, forgot the INDIRECT(...) ..)

Note: In the preceding suggestion, the range AP10:AP40 was ignored. If you
also have "hours worked" in that range to be conditionally summed in the same
manner, just make a duplicate of the sheet: Summary, and change the range to
sum to point to "AP10:AP40" instead,

i.e. Put in B2 (in the duplicated sheet):

=SUMPRODUCT((INDIRECT("'"&B$1&"'!$AN$10:$AN$40")= TODAY()-$A2)*(INDIRECT("'"&B$1&"'!$AN$10:$AN$40")<TODAY()) ,INDIRECT("'"&B$1&"'!$AP$10:$AP$40"))

Then fill across and down to populate the table as before

This will return the corresponding results for the range "AP10:AP40"

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sorry, Wayne. Afraid I lost you somewhere along the line <g
Perhaps you would like to email over a sample copy of your book,
to either:

demechanik <at yahoo <dot com
or
xdemechanik <at yahoo <dot com

In the interim, hang around your post for possible responses from others
(I was probably off-target in the interp of your original post)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kane" wrote:

Max .. Tks for the help. I should have provided better info to start with.
AN10:AP40 contain hours worked
AQ10:AQ40 contain days of the month
AN9 contains 30 days
AO9 contains 90 days
AP9 contains 365 days (12 months)
AQ9 contains days of the month

sheets are noted, Jan, Feb, Mar ... 2005 Summary

Year "2005 Summary" Sheet, has numerous accumulated records for the past yr.
The 30, 90, 365 day column's have a column header =NOW()
k3 contains =Now()
K4 contains 30 days
L4 contains 90 days
M4 contains 365 days

By year, A20 contains the current Year, and row 20 has many year SUM totals.
I would like to indicated the =Now() 30, 90, 365 days hours worked in:
K3 contains =NOW()
K4 30 day
L4 90 day
M4 365 day

K20 ? =NOW() hours worked
L20 ? =NOW() hours worked
M20 ? =NOW() hours worked

It would really make my day to solve this!

Again, Merci, Thanks for the help!

Wayne ... Montreal Canada

Sample file returned to Wayne, with ..

Implemented in "2005 Summary"
-----------------------------
Put in K20:

=OFFSET(INDIRECT("'"&CHOOSE(MONTH($K$3),"Jan","Feb ","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct", "Nov","Dec")&"'!$AQ$9"),MATCH(DAY($K$3),INDIRECT(" '"&CHOOSE(MONTH($K$3),"Jan","Feb","Mar","Apr","May ","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"'!$A Q$10:$AQ$40"),0),COLUMNS($A$1:A1)-4)

Copy K20 across to M20

The formulas in K20:M20 will read the date in K3 (i.e. it reads the month
and the day in the date), and retrieve the values from the appropriate
monthly sheet, and from the row corresponding to the day in K3

Notes:
1. Cell K3 contained : =NOW()
2. Monthly sheets are named as: JAN, FEB, ... DEC
3. Range $AQ$10:$AQ$40 in each monthly sheet contains the numbers: 1. 2. ...
31
4. Corresponding lookup values to be retrieved were in 3 cols to the left of
$AQ$10:$AQ$40 in each monthly sheet, with the col positions corresponding to
"30 days", "90 days" and "12 mon" cols in "2005 Summary"

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----