Excel spontaneously changes reference to linked workbook

[kafka]

I have a cell in a worksheet which is linked to a cell in another workbook. In theory the link should update when I open the first workshheet, however the linking formula has changed itself to c:\Users\Michael\AppData\Roaming|Microsoft\Excel\file name and cell reference. It is supposed to link directly to a cell in a workbook in my D: drive and ,because it doesn't, the result in the cell is outdated. I have manually changed the formula to directly link to the appropriate cell but Excel simply changes it back. Help!

 

[Jon von der Heyden]

Cross psoted here: https://www.excelforum.com/excel-fo...ly-changes-formula-linking-two-workbooks.html

Per moderator reply at Excel Forum - we too require that you include links to cross-posts. This is so that possible contributors can see what suggestions have already been offered.

 

[kafka]

My Excel Forum link is https://www.excelforum.com/excel-fo...ly-changes-formula-linking-two-workbooks.html

 

[kafka]

Afte trying again to link spreadsheets I have finally been successful (though I don't know why).

 

[kafka]

No, I haven't been successful. The linking formula has changed itself again to c:\Users\Michael\AppData\Roaming|Microsoft\Excel\file name and cell reference. It is supposed to link directly to a cell in a workbook in my D: drive

 

[kafka]

I gather that nobody has an explanation for this phenomenon. My latest idea is to change the linking formulas back to what they are supposed to be (see earlier posts) then password protect the worksheet. Maybe this will stop Excel from changing the formulas.

 

[kafka]

My last attempt to fix the problem failed.

 

[kafka]

An update. In an attempt to solve my problem I created a macro which recreates the two links each time it is run. While washing up this morning a possible reason for the problem popped into my head. When setting up the links I have always opened the second workbook by selecting it from the list of recently opened workbooks which appear when I click on Files Open. It occurred to me that Excel might be confused if, days later, it didn't find the desired workbooks in the recently opened files list. As well as the aforementioned macros, I have now linked to the other workbooks by selecting them directly from My Computer. I will see which of the methods work (if either does).