Solved: links

[tkaplan]

I'm having a very strange thing happen:

I have a workbook which is used to invoice an agency and it is used every month based on data in the different sheets, one for each month.

so i have a sheet for each month, named september05, october05, november05, december05, etc.
These sheets are identical - i use the same one every month, just change the names.
in these worksheets, i have a named range named "FirstDayOfMonth". this is a date.

i have another workbook named invoice. this workbook has a cell (c3) which looks at the "FirstDayOfMonth" from the sheet that it is linked to. so if it's linked to september05.xls, c3 should say "September 2005" based on the date 9/1/2005 which is the value of FirstDayOfMonth in that sheet. in the invoice workbook, i have a button which the user clicks so that they can change the linked workbook to a different month. they find the correct book, say October, and c3 changes to October 2005.
the problem:
through October, it all works fine. when i try linking to november or later, c3 in invoice comes up as January 1900.
Does anyone have any idea why this is happening? when i link to one sheet it's recognizing the name, when i link to another, it no longer recognizes it???

thanks in advance
tkaplan

[austenr]

Have you checked the formatting in the November and on sheets?

[tkaplan]

yes. they are both the same. formatting has not changed.

i am working on modifying the sheets that i can attach them (removing all confidential stuff, etc.)

[tkaplan]

ok, here are the modifies sheets.

if you open up the file named _Summit county roster, it is currently pointing to the October file. The month says october (in cell C4) and in columns F and G the dates correspond to the five weeks in the October sheet.
if you click change month, and change the source to the november sheet, it should switch it to the corresponding november dates and say november. it sets everything to january.

in the actual sheets, in the Summit Cty DJFS cell V2 is the named range of FirstDayOfMonth.
i cant figure out for the life of me why it works on one month and not the other!

you can ignore all of the other buttons, macros, etc.

Please help!!!

Thank you,
tkaplan

[geekgirlau]

There's an extra row in November - delete row 1 on the Summit Cty DJFS sheet and it should work.

[samohtwerdna]

Your date format was indeed the problem. - I changed the format from *3/4/01 - to 03/04/01 - and the date come up fine.

I did also delete row #1 on the November sheet, but I dont think that was your problem. Jan - 0 - 1900 is the date value for 0 (you probably already know that) but when ever I get this date I know my cell is looking for a date or a value and is only finding 0 or null - often formula's that produce a false value are the reason

Hope this helps,

[tkaplan]

I tried changing the format and it didnt make a difference. the october one has the same format.

I did a cut and paste of the cell into row 1 instead of 2 and now it works.
why would it make a difference which row it's in if it's looking for a named range anyway????

[geekgirlau]

I think you need to be careful where both the workbooks have the same range name, but defined as different targets (it was row 1 in one workbook and row 2 in the other).