Difficulty in Linking to Unopened XLSM Workbooks

I am new to this forum. I have seen MANY postings on this matter, all having one thing in common. Namely, there is no explanation nor solution.

Recently upgrading from 2003 to 2010, we have many xlsm workbooks. Some will have cells which reference cells of another workbook. The cell formula may be:
='C:\Temp\[My_Workbook.xlsm]Sheet1'!$B10
If the source file is open, all is well. But otherwise, such a cell will show up as "#REF!" and upon going through "Edit Links", the source file will show "ERROR: Source File not Found". The only way I have found around this is to save all workbooks as type xls. To do this, however, must prevent you from taking advantage of the 2010 version fully since we are using the 2003 format.

What's more scary is that you can leave the values of the external reference (as of the last time the file was saved and both files were opened) by using the "Save external link values" option. But if you do not have the source workbook open and its values have changed, these new values will not be what is shown. Rather than the link being updated, it will simply show what values were there previously with no message or warning that the values shown may not be current. So when can we trust those values to be correct?

Does anyone know whether this is truly an accepted deficiency of the 2010 version which will never be fixed? Can we have external links to xls files but not to xlsm files? Thank you in advance for any input.

Steve Andrews

If the source file is open, all is well. But otherwise, such a cell will show up as "#REF!"

Strange as this should not be any problem as far as I know. See link below and check pages 5 and 6 for more information.

https://training.health.ufl.edu/hand...10-Linking.pdf

Alf

Thanks, Alf, for the document. But the problem remains. If the file being linked to is type xlsm and it is not open, then the links cannot be updated. I've gone through the many operations of the document. Strangely, if you press "Change Source" in the Edit Link Window and select the file you wish to link to (avoiding manual typing of the path or filename), it still shows "Source not found".

We've decided to simply change all workbooks to type xls. The linking works fine with xls and the only drawback seems to be file size. Regardless, thanks again for your response.

Steve