Excel 2003 Cells Returning #VALUE from linked files

[canary]

Hi,
I recently migrated from OfficeXP to Office2003 and upon openening my spreadsheets in Excel2003 those files with linked references to other spreadsheets only display in the cells #VALUE. However, if I goto those linked spreadsheets and open and save them then the #VALUE vanishes and the number appears again. Surely, there must be some simple way to fix up the #VALUE error?

 

[macropod]

Hi Canary,

It may just be a matter of changing your Excel settings - make sure 'update remote references' and 'save external link values' are checked, under Tools|Options|Calculation.

Cheers

 

[canary]

Hi,
Macropod! Thanks for the reply. I wish it were that simple. I have been sratching my head about this one and just cannot understand why it is doing this. Any more thoughts or ideas?

Cheers!!

 

[macropod]

Hi canary,

What functions are being used by the formulae that have this problem?

Cheers

 

[canary]

Hi macropod,
Simple ones just SUMIF. But if these are run on OfficeXP just the same they work, just after in 2003 just return #VALUE. Oh, as mentioned if I open those link source spreadsheets and just hit save they master file works. But surely this cannot be the final solution.

Thanks!!!!

 

[KenWright]

If you open a file in Excel that was created in an earlier version it will recalculate upon opening so if you haven't let it update links to valid files then it may well fall over trying to reference these values. There's an option in Edit / Links from XL2002 on to allow you to tell it not to update links or even ask for them, but not sure if this will even help. Might be worth a look though.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[canary]

Ken,
Thanks for the reply. All the link files are valid files and plus we do need to update them upon opening. Just don't get it that the files worked fine in OfficeXP and now in Office 2003 just don't seem to work.

Thanks!!!

 

[KenWright]

Give us some examples of formulas that are returning the error.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[canary]

Just simple SUMIF functions. Worked ok in XP but not 2003. Just funny that if we save the sheets in Excel 2003 and change the Calculation option to Manual the #VALUE become the correct numbers.