In his Smurf on spreadsheets blog, Simon Murphy highlighted a data linking anomaly that causes reference errors when you open a worksheet that contains the source cell for an external link

As described by Smurf [1], the problem can be replicated by opening two workbooks and saving them as .xlsm files.

If you put text in a cell on one workbook and link to it from the other and then save and close both workbooks, when you reopen them (source file first), Excel 2007 warns that you are opening a file that contains a link.

When you click the Update button, Excel replies, "Source not found"

A Microsoft support article [2] explains that the problem arises when Excel 2007 recalculates a workbook previously saved in an earlier version of Excel, and in doing so forces updates of all external references.

The recommended workaround is to check each link source in the workbook to see if the data source is available and to insert the correct path in the link formula. If any link sources are no longer available, either change the link formula to point to an alternative source or remove it completely.

But this may not explain why and how the situation arises within native Excel 2007 workbooks, as experienced by Smurf. Commenting on the post, Harlan Grove suggests that the problem may relate to how Excel 2007 refers to the link source books in its internall address. "If I save Book2 as Book2 rather than as Book2.xls (it take some work to do so - you have to enter the filename as "Book." including the double quotes AND the ending period), then I reopen Book1.xls, it opens as expected, automatically updating the link to [Book2]Sheet1!A1."

He comments: "From my perspective Excel is making a proper distinction between one file named Book2 and another named Book2.xls. That’s why a saved reference to Book2 rather than to Book2.xls in Book1.xls is properly treated as a problem if there’s a Book2.xls but not a Book2."