Automatic changing refrences to sheet corresponding to first day.

[Guest]

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?

 

[Max]

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","Tuesday","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

 

[Guest]

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]

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","Tuesday","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)&"'!D52")),""))

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

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

=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

 

[Max]

Typo in line:

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

"G4 & H4" should read: G1 & H1

 

[Guest]

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]

So why does the selected values from Spinbox work but

selection using a combo box does not work??

Both should work.

Maybe take a look at the revised sample file below:
http://flypicture.com/p.cfm?id=50955
(Right-click on the link: "Download File"
at the top in the page, just above the ads)
File:Firebird_misc_3.xls

There's now a combo-box positioned over H1 which has the cell link in I1,
input range in Index!$A$1:$A$10. H1 contains the formula:
=INDEX(Index!A:A,I1) to return the corresponding selection made from the
input range. Spinner over G1 controls the month# appearing in G1. Font color
in H1 and I1 is gray, to blend with fill color and mask cell displays.

You would notice that unlike the spinner, which outputs the month# directly
into the cell link G1, the combo box requires say, an INDEX formula to read
the cell link number returned in I1, and hence return a year value in H1.
And the value returned in H1 must be a real number, not a text number,
otherwise the formulas reading H1, e.g. .... YEAR($B2)=$H$1 would not
evaluate correctly, and nothing will be returned.

To coerce any text numbers which may be present in YEAR_TABLE to real
numbers, try copying an empty cell, then select the input range for the
combo box, and right-click > paste special > Add > OK.

 

[Guest]

Thanks for your response. I also used the VALUE(LD_YEAR) to refrence YEAR
selected from Combo Box and it worked.
But I will copy the values for YEAR_TABLE as you suggested.
You have been a great help.

 

[Max]

You're welcome !
Thanks for the feedback ..

 

[Guest]

Max,
A strange thing happened and I am puzzled now.
When I saved the workbook with a different name, the formuls are returning
null values for each day in the summary sheet.
What went wrong here???

 

[Guest]

I realized it is the reference to where year selection is that is causing the
problem.
YEAR($B2)=$H$1 was failing in my workbook as soon as I save the workbook
under a different name. $H$1 is linked to a combo box but obviously the
formatting of that cell changes when i save the workbook under different
name. The selected Year value (i.e., 2005) gets left aligned instead of right
aligned as is in the working file.
I had copied an unused cell and did the paste special-add to H1 where year
is linked from combo-box and all values appear again under Inbound and
outbound.
What do I need to do so I do not have to keep copy and paste speciall-add a
blank cell to H1 when the workbook is initialized for a new month and saved
under a different name???
I know I could use spinbox to select years between 2000-2010 and I do not
have problem displaying the proper values.
Any suggestion is highly appreciated.

 

[Max]

Not sure what's happening over there <g>, but perhaps one quick fix to try
would be to replace "$H$1" in the formulas with "$H$1+0". The "+0" should be
enough to coerce the text numbers that seems to be returned in H1 (text
numbers would appear "left aligned") to real numbers.

2 ways to effect this:

The simpler try:

Change the formula in H1 from:

=INDEX(Index!A:A,I1)

to:

=INDEX(Index!A:A,I1)+0

Alternatively, we could of course, change the formulas in cols C to E
(formulas in C2:E2, copied down) to these instead:

In C2:
=IF($B2="","",IF(AND(MONTH($B2)=$G$1,YEAR($B2)=$H$1+0),IF(ISERROR(INDIRECT("
'"&DAY($B2)&"'!D52")),"",INDIRECT("'"&DAY($B2)&"'!D52")),""))

In D2:
=IF($B2="","",IF(AND(MONTH($B2)=$G$1,YEAR($B2)=$H$1+0),IF(ISERROR(INDIRECT("
'"&DAY($B2)&"'!D62")),"",INDIRECT("'"&DAY($B2)&"'!D62")),""))

In E2:
=IF($B2="","",IF(AND(MONTH($B2)=$G$1,YEAR($B2)=$H$1+0),IF(ISERROR(INDIRECT("
'"&DAY($B2)&"'!D31")),"",INDIRECT("'"&DAY($B2)&"'!D31")),""))

then select C2:E2 and fill down

(Col F's formulas - no change)

 

[Guest]

Hi Max.
Thanks for your suggestion. I have encountered another problem.
Actually the Summary Sheet is copied into a separate file by itself and
emailed to the management on a daily basis. With these formula containing
INDIRECT reference to daily hours I am unable to get the Hours displayed.
Copy of the sheet comes with null values in the INBOUND and OUTBOUND hours
same problem that I encountered earlier with saving of file under different
name.
This time the culprit seems to be the INDIRECT construct in the formulas.
I am totally puzzled now. I ended up copying and pasting the values of
INBOUND and OUTBOUND hours from the original Summary Sheet.
Any thoughts??

 

[Max]

Why not just make a frozen copy of the Summary Sheet (via an entire sheet >
copy > paste special > values [ & formats]) for the mgt report purpose?

In that way, you'll avoid a lot of complications. So you hold the
operational file at your end, and make / send the frozen copy (no formulas).

 

[Max]

Actually the Summary Sheet is copied

into a separate file by itself ...

INDIRECT requires that the referenced source sheets (i.e. the "1","2","3"
.... daily sheets) -- since these are now in *another* file -- to be open
simultaneously, otherwise #REF! errors will be returned by INDIRECT, and the
IF(ISERROR(INDIRECT(...) .. then ultimately returns blanks: "" as the
results. So, of course, nothing (blanks) will show when the Summary Sheet
is open on its own <g>. Consider the earlier suggestion to freeze the
Summary Sheet ..

 

[Guest]

Thanks a lot for your help. I had already implemented what you suggested as
making a frozen copy. I have provided a button which runs a macro to copy
the sheet in a new workbook and then copies and pastes values of hours for
each days under different activities and this works fine.

 

[Max]

Glad to hear that !
The feedback is appreciated.