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?