Excel spontaneously changes formula linking two workbooks

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!

Disclosure: I have posted this query in Mr. Excel but have not received any replies.

The link to Mr.Excel is https://www.mrexcel.com/forum/excel-...-workbook.html

Yes, my profile is up to date.

Both workbooks are in the same folder on the same drive on the same computer. There are two links, actually, both with the same problem. The links should be:

D:\[DISCOGS.XLSM]SOLD!'$N$1 and

D:\'[EBAY SALES.XLSM]TOTAL'!$C$1

I have just re-entered the formulas and they have not yet changed. This happened the last time I corrected the link(s). The change to roaming didn't happen straight away. I will see if it has occurred tomorrow morning.

Thanks AliG,

I had typed in my formulas so the error in syntax was merely a typing mistake. I have redone my linking formulas, changing the cells to which they were linked and they have not revised themselves to the mysterious Roaming folder. Fingers crossed!

The same problem has recurred. The linking formula, which should refer dirtectly to a cell in my D: drive, once again shows c:\Users\Michael\AppData\Roaming|Microsoft\Excel\file name. Why does it refer to a cell in a workbook in my C: and what does roaming refer to?

Yes, I'm still using Excel 2007 and I am using onlt the one computer.

If I remember correctly, it is a drive parition. It was created to be my data drive (i.e. it contains all my images, spreadsheets, etc.).

Device Manager shows D: drive as a primary partition. Does this help? I have attached the workbook containing the errant formulas (indicated by the arrows).

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.

This didn't work. I guess nobody has any ideas.

It is very mysterious. I only open these files on the one computer. All of the files are located in the same folder on my D: drive and I can't think of anything I do which would change the links.

Perhaps i should see if I can find the phantom files in the C: drive roaming folder and delete them. It also occurred to me that I could create a macro which opens and closes the linked files. If that didn't make a difference I could create a macro which enters the links anew each time I click on the new macro button (which I would label "Update"

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).