Excel 97 - updating links on file open returns #N/A

[pdem]

My summary file links to 10 subfiles. When I open the summary file and update the links, certain links return #N/A. I have also tried automatically updating links in Tools, options, edit with the same results.

The formula in cell B24 is
='H:\[PO 6769.xls]Summary'!B3
which returns #N/A instead of 6769-111
while the formula in cell B25 is
='H:\[PO 6769.xls]Summary'!B4
which correctly returns the string 6769-112.

However all the links that return numbers work. Opening the source file properly updates the link but, on saving and closing both files and opening the summary file, the error returns.

Exactly half of the source files return an error.

My settings under Tools, options, calculation are automatic and update remote references.

I applied service patch 2 for Office 97 and updated to the latest Excel 97 patches available.

I run Windows XP Home sp2.

I have used this site many times and have always been able to find answers to my problem. Great site and thanks!!!!!

 

[GlennUK]

Hi, have you made sure that the supporting files that are causing the problems are saved as the same Excel version as the others ( check using File Save As, as look at the file type being saved as ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[pdem]

Hi Glenn

Yes all my files are saved as workbooks. The subfiles were all based on one file, which I saved under 10 different names. They were then modified by adding or deleting a few lines as necessary (and changing data).

I thought that I might have formatted the source cell as text or something like that (it's formatted general), but the original source file is one of the problem files, but some of the resulting files do not have the problem, ie I did not change the format of that cell.

Perry

 

[GlennUK]

You say they are all saved as workbooks .... but you haven't said that you've checked the Excel version that they are saved as. Have you done that check?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[pdem]

Glenn

I checked all the files, the file type is "Excel Workbook Workbook (*.xls). This is the default line. Further down the list I see Excel 95/5.0 Workbook and EXcel 97/95/5.0 workbook, but none of my files were saved under these options.

I tried to experiment by saving a file under Excel 97 type but it said it could not save under the same name and created a different workbook file. I then opened the file but got the message 'cannot access [file]'.

I was able to work around some of my errors by typing over the source cell, saving, then reytping my correct data. But this did not work with all of the errors.

 

[GlennUK]

Sorry, no more ideas here .... assuming you are using direct links, and not complex formula to link ( you know using OFFSET or VLOOKUP or some such ).



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.