Keep getting #REF! on updated cells

[InsaneTech]

Hi,
The issue im getting is with updated spreadsheets with links to other spreadsheets.
Let me give you an example:

Spreadsheet A and B are two different excel files.
Spreadsheet A comes from a report generated by a monitoring program saved as a xls.
Spreadsheet B has cell links to spreadsheet A, and spreadsheet B is the main collection sheet for all data, saved as a xlsx.
Now every friday, the monitoring program sends through new weekly spreadsheet A via email, which is automaticly saved over the exisiting spreadsheet A on the drive.
When this happens, spreadsheet B then comes up with #REF! in the cells that link to spreadsheet A.
the only way to clear it and get the updated data on spreadsheet B, is by opening up spreadsheet A and pressing save, although its already saved on the drive.

Is there a way i can automaticly update the data on spreadsheet B without having to open spreadsheet A every friday and saving it?

Thanks

 

[shadow12345]

Im not sure what might cause that... but maybe you could just record a macro of you opening Spreadsheet A and saving it.

The just run that instead... not really a solution, but it would at least make it less of a pain.

 

[InsaneTech]

Thanks, i will give that a go, but effectivly i need this to work automated as i have to get it working on several computers.
Running a macro is easy enough, but it still takes up time.
any other ideas??

 

[InsaneTech]

I have looked into this a bit more, and all that i have found so far is that by actually copying the cell from spreadsheet A then paste special "link" on spreadsheet B, that it will update the Cells on A if someone else changes the cells in B. but i am still with the issue that by overwriting the file with the updated save, i still get a #REF! error.

This is quite actually driving me insane! it cant be this hard!