I have a monthly workbook that is used to input daily labor hours
distribution for different activities. There is sheet for each day titled "1"
for day one of the month.
A simplified format of the data is as follows for each day
Col 1 Col2
Actvity Code 1 Hours charged
..... .......
Actvity Code n Hours Charged
Total Sum of All Hours
Activity codes are grouped into Inbout, Outbound and Others and there is a
subtotal of hours spent in each group on a daily basis in a fixed cell for
each group.
I am linking the sum of each group into a Summary sheet that provides the
Summary for each day from the day one to the last day of the month.
Here is a simplified version of the Summary Sheet:
Day Date Inbound Hours Outbound hours Other Hours Total
Monday 03/28/05 0 0 0
0
Tuesday 03/29/05 0 0 0
0
......
Friday 04/01/05 ="1"!$D$52 ="1"!$D$62 ="1"!$D$31
=SUM(C6:E6)
Saturday 04/02/05 ="2"!$D$52 ="2"!$D$62 ="1"!$D$31 =SUM(C7:E7)
Sunday 04/03/05 ="3"!$D$52 ="3"!$D$62 ="3"!$D$31
=SUM(C8:E8)
Week1 Total
Monday 04/04/05 ="4"!$D$52 ="4"!$D$62 ="4"!$D$31
=SUM(C10:E10)

and so on.This sheet is summarising on a weekly basis, meaning the staring
day is Monday.
Every month I have to manually update the formula to point to the first day
of the month. For example in April 2005, Friday was April 1, so I delete
formula from Monday thru Thursday and change the reference to sheet "1" in
the row for Friday and so on.
I like to be able to do it by click of a button when I initialize the
workbook for new month. Remember also in the last week of the month I might
have to wipe out the formulas for the days which are outside the month being
reported.
Is there a way to accomplish this without manually changing the formuls on
the Summary Sheet every month?

In the Summary sheet: Assuming the dates are in col B, B2 down
with cols C to F for: Inbound Hours, Outbound Hours, Other Hours and Total
(Labels in C1:F1)

Put in A2:
=IF($B2="","",CHOOSE(WEEKDAY(B2,2),"Monday","Tuesd ay","Wednesday","Thursday"
,"Friday","Saturday","Sunday"))
Copy A2 down to A32

Put in C2:F2
=IF($B2="","",INDIRECT("'"&DAY($B2)&"'!D52"))
=IF($B2="","",INDIRECT("'"&DAY($B2)&"'!D62"))
=IF($B2="","",INDIRECT("'"&DAY($B2)&"'!D31"))
=IF($B2="","",SUM(C2:E2))
Select C2:F2, fill down to F32

So for each month's summary, with all the "day" sheets: 1,2,3... 31 for the
month in the same book, all you need to do is just re-enter the first date
in B2, and fill down.

For the dates in col B, col A will return the day, cols C to E will return
what is required from each of the 1 - 31 day sheets and col F computes the
total per day.

Here's a sample file for the above construct ..:
http://flypicture.com/p.cfm?id=48780
(Right-click on the link: "Download File" at the top in the page, just above
the ads)
File: Firebird_misc_1.xls

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Firebird" wrote in message
...
I have a monthly workbook that is used to input daily labor hours
distribution for different activities. There is sheet for each day titled
"1"
for day one of the month.
A simplified format of the data is as follows for each day
Col 1 Col2
Actvity Code 1 Hours charged
.... .......
Actvity Code n Hours Charged
Total Sum of All Hours
Activity codes are grouped into Inbout, Outbound and Others and there is a
subtotal of hours spent in each group on a daily basis in a fixed cell for
each group.
I am linking the sum of each group into a Summary sheet that provides the
Summary for each day from the day one to the last day of the month.
Here is a simplified version of the Summary Sheet:
Day Date Inbound Hours Outbound hours Other Hours
Total
Monday 03/28/05 0 0 0
0
Tuesday 03/29/05 0 0 0
0
.....
Friday 04/01/05 ="1"!$D$52 ="1"!$D$62 ="1"!$D$31
=SUM(C6:E6)
Saturday 04/02/05 ="2"!$D$52 ="2"!$D$62 ="1"!$D$31
=SUM(C7:E7)
Sunday 04/03/05 ="3"!$D$52 ="3"!$D$62 ="3"!$D$31
=SUM(C8:E8)
Week1 Total
Monday 04/04/05 ="4"!$D$52 ="4"!$D$62 ="4"!$D$31
=SUM(C10:E10)

and so on.This sheet is summarising on a weekly basis, meaning the staring
day is Monday.
Every month I have to manually update the formula to point to the first
day
of the month. For example in April 2005, Friday was April 1, so I delete
formula from Monday thru Thursday and change the reference to sheet "1" in
the row for Friday and so on.
I like to be able to do it by click of a button when I initialize the
workbook for new month. Remember also in the last week of the month I
might
have to wipe out the formulas for the days which are outside the month
being
reported.
Is there a way to accomplish this without manually changing the formuls on
the Summary Sheet every month?

Thank you Max for your response, I got bogged down with some S-Ox related
stuff and could not respond sooner.
How do I prevent reference to values from April 28 being shown in values
for March 28?? I want to show in the dates for March either zero values or no
values at all as I show in the example. Since my summary sheet is formated to
show data for each week from Monday to Sunday so for First week my Summary
sheet will have rows for March 28, 29, 30 and 31 before the row for April 1.
Your formula works fine from April 1 to April 30 but it does not avoid
filling values for March dates from april days. Same way I want to be able to
automatically fill the rows after April 30 with zero values and not fill the
row for May1 to get the data from April 1.
To sum it I need a formula that will reference the values from detail sheets
only if the row belongs to the month being reported.
I hope I am making it clear and not confusing.
Once again your help is highly appreciated.

"Max" wrote:

In the Summary sheet: Assuming the dates are in col B, B2 down
with cols C to F for: Inbound Hours, Outbound Hours, Other Hours and Total
(Labels in C1:F1)

Put in A2:
=IF($B2="","",CHOOSE(WEEKDAY(B2,2),"Monday","Tuesd ay","Wednesday","Thursday"
,"Friday","Saturday","Sunday"))
Copy A2 down to A32

Put in C2:F2
=IF($B2="","",INDIRECT("'"&DAY($B2)&"'!D52"))
=IF($B2="","",INDIRECT("'"&DAY($B2)&"'!D62"))
=IF($B2="","",INDIRECT("'"&DAY($B2)&"'!D31"))
=IF($B2="","",SUM(C2:E2))
Select C2:F2, fill down to F32

So for each month's summary, with all the "day" sheets: 1,2,3... 31 for the
month in the same book, all you need to do is just re-enter the first date
in B2, and fill down.

For the dates in col B, col A will return the day, cols C to E will return
what is required from each of the 1 - 31 day sheets and col F computes the
total per day.

Here's a sample file for the above construct ..:
http://flypicture.com/p.cfm?id=48780
(Right-click on the link: "Download File" at the top in the page, just above
the ads)
File: Firebird_misc_1.xls

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Firebird" wrote in message
...
I have a monthly workbook that is used to input daily labor hours
distribution for different activities. There is sheet for each day titled
"1"
for day one of the month.
A simplified format of the data is as follows for each day
Col 1 Col2
Actvity Code 1 Hours charged
.... .......
Actvity Code n Hours Charged
Total Sum of All Hours
Activity codes are grouped into Inbout, Outbound and Others and there is a
subtotal of hours spent in each group on a daily basis in a fixed cell for
each group.
I am linking the sum of each group into a Summary sheet that provides the
Summary for each day from the day one to the last day of the month.
Here is a simplified version of the Summary Sheet:
Day Date Inbound Hours Outbound hours Other Hours
Total
Monday 03/28/05 0 0 0
0
Tuesday 03/29/05 0 0 0
0
.....
Friday 04/01/05 ="1"!$D$52 ="1"!$D$62 ="1"!$D$31
=SUM(C6:E6)
Saturday 04/02/05 ="2"!$D$52 ="2"!$D$62 ="1"!$D$31
=SUM(C7:E7)
Sunday 04/03/05 ="3"!$D$52 ="3"!$D$62 ="3"!$D$31
=SUM(C8:E8)
Week1 Total
Monday 04/04/05 ="4"!$D$52 ="4"!$D$62 ="4"!$D$31
=SUM(C10:E10)

and so on.This sheet is summarising on a weekly basis, meaning the staring
day is Monday.
Every month I have to manually update the formula to point to the first
day
of the month. For example in April 2005, Friday was April 1, so I delete
formula from Monday thru Thursday and change the reference to sheet "1" in
the row for Friday and so on.
I like to be able to do it by click of a button when I initialize the
workbook for new month. Remember also in the last week of the month I
might
have to wipe out the formulas for the days which are outside the month
being
reported.
Is there a way to accomplish this without manually changing the formuls on
the Summary Sheet every month?

Perhaps this revision might do it for you ...
(Link to sample file is provided below)

In the Summary sheet:

Let's reserve cell G1 for input of the "current" month of interest (input as
a number, 1 = Jan, 2 = Feb, etc). For the sample example (i.e. Apr), input
in G1: 4
And cell H1 will be reserved for input of the year, e.g. input: 2005

Put in A2 (no change):
=IF($B2="","",CHOOSE(WEEKDAY(B2,2),"Monday","Tuesd ay","Wednesday","Thursday"
,"Friday","Saturday","Sunday"))

Put in C2:F2 (revised formulas)

=IF($B2="","",IF(AND(MONTH($B2)=$G$1,YEAR($B2)=$H$ 1),IF(ISERROR(INDIRECT("'"
&DAY($B2)&"'!D52")),"",INDIRECT("'"&DAY($B2)&"'!D5 2")),""))

=IF($B2="","",IF(AND(MONTH($B2)=$G$1,YEAR($B2)=$H$ 1),IF(ISERROR(INDIRECT("'"
&DAY($B2)&"'!D62")),"",INDIRECT("'"&DAY($B2)&"'!D6 2")),""))

=IF($B2="","",IF(AND(MONTH($B2)=$G$1,YEAR($B2)=$H$ 1),IF(ISERROR(INDIRECT("'"
&DAY($B2)&"'!D31")),"",INDIRECT("'"&DAY($B2)&"'!D3 1")),""))

=IF($B2="","",IF(SUM(C2:E2)=0,"",SUM(C2:E2)))

Put a starting date in B2, e.g.: 28-03-2005

Select A2:F2 and fill down as needed to cover the entire year and beyond

For each month's summary, with all the "day" sheets: 1,2,3... 31 in the same
book, you just need to change / input the month and year in G4 & H4, and the
desired results will be returned.

For the dates in col B, col A will return the day, cols C to E will return
what is required from each of the 1 - 31 day sheets and col F computes the
total per day.

Here's a sample file for the above revised construct ..:
http://flypicture.com/p.cfm?id=50046

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: Firebird_misc_2.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Firebird" wrote in message
...
Thank you Max for your response, I got bogged down with some S-Ox related
stuff and could not respond sooner.
How do I prevent reference to values from April 28 being shown in values
for March 28?? I want to show in the dates for March either zero values or
no
values at all as I show in the example. Since my summary sheet is formated
to
show data for each week from Monday to Sunday so for First week my Summary
sheet will have rows for March 28, 29, 30 and 31 before the row for April
1.
Your formula works fine from April 1 to April 30 but it does not avoid
filling values for March dates from april days. Same way I want to be able
to
automatically fill the rows after April 30 with zero values and not fill
the
row for May1 to get the data from April 1.
To sum it I need a formula that will reference the values from detail
sheets
only if the row belongs to the month being reported.
I hope I am making it clear and not confusing.
Once again your help is highly appreciated.

Typo in line:
... you just need to change / input the month and year in G4 & H4

"G4 & H4" should read: G1 & H1
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks for the formulas. I adapted your formulas to my needs. At first I was
getting no results but after some tries I found the culprit. In the real
application, the user selects the year from a drop down (combo box). The
Combo Box displays the years from 2000 thru 2012 from a 1 column table named
YEAR_TABLE. All the cells have General Format. The reesult of the selection
is in linked cell named LD_YEAR which also has a General Format.
So instead of absolute refrence I am using the named reference.
If I type the year, it works fine but not as a selection from the drop down.
For month's selection user uses a spinbox to select month which is linked to
cell named LD_MONTH. So why does the selected values from Spinbox work but
selection using a combo box does not work??

"Max" wrote:

Typo in line:
... you just need to change / input the month and year in G4 & H4

"G4 & H4" should read: G1 & H1
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----