Date Formatting Problem

[Thomas5000]

I'm having some date formatting problems. I've looked at the following thread but was not able to resolve my problem.

http://www.tek-tips.com/viewthread.cfm?qid=1409951

My excel file is found at this location. I could not upload it while on my work computer.

http://www.wingfling.org/excel/forms.htm

When i place an = sign in Col A pointing to a date value, it works perfectly fine. But from Col B to Col H, it places a 0 or 00/00/00 in the cell. After Col H it places #Value! in teh cell.

I tried the instructions on this link but it either didn't work or i didn't fully understand it.

http://www.tek-tips.com/viewthread.cfm?qid=1409951

Thanks.

 

[AnotherHiggins]

First - I cannot tell you how much I hate merged cells. They are the source of many a headache. And your situation is a great example. So right off the bat, unmerge the cells.

"But they need to look like that" you may say.

No problem. Highlight the cells you would normally merge and, instead, change the Horizontal Alignment to Center Across Selection. It looks just like they're merged, but this is much easier to deal with. Want proof?

Now go to the first sheet (PROBLEM EXPLAINED) and link to A4 . Notice that you can actually just link to A4 now, instead of the Excel automaticly entering the reference "='Jan W1'!A4:H4" (as it did with the merged cells).

You will have to properly format the cell to display a date, but it works now.

See? Merged cells are evil, I tell ya.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

[tt]
='Mar W4'!A4:H4
[/tt]
is not a valid CELL formula. It is an ARRAY. Never mind that A4:H4 is merged.
[tt]
='Mar W4'!$A$4
[/tt]
You must make the range reference ABSOLUTE.

IMHO, your workbook design will yield nothing but ongoing blood, sweat and tears as it makes data analysis& reporting, which it seems you are attempting to do, EXTREMELY difficult, since by the nature of your design, thwarts the data analysis & reporting features of Excel.

Your weekly data ought to ALL reside in ONE table, with a column for a REAL DATE that represents the week.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Thomas5000]

Thanks guys! Skip always appreciated. I'll give some thought to this. These used to be separate files and I'm grouping them together as one. I'm going to reread your post adn see if i can understand it and apply it.

I think that Orientation only takes place 2 weeks out of the month and that some of the dates are wrong. I'm just trying to throw something together quite quickly that will give me the info i need. BUT I DEFINITELY PLAN TO REFINE IT. Any suggestions are greatly appreciated.

Thanks!

 

[SkipVought]

In reality, your weekly sheets are themselves aggregations. It would be better to get the source data from which the data was generated.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]