I have two excel files with links to each other, both saved in the same folder in OneDrive.
Here is an excerpt of one of the formulas when I first created the links:
...SEARCH("IVC",INDEX('[Live Pipeline (v2.00).xlsm]Project View'!$A$1:$FZ$50...
However after closing and opening the file, they all changed automatically to:
SEARCH("IVC",INDEX('https://MYORG.sharepoint.com/Shared Documents/Sales/[Live Pipeline (v2.00).xlsm]Project View'!$A$1:$FZ$50
and changed so the value they return is #VALUE
If I do a Find/Replace on https://MYORG.sharepoint.com/Shared Documents/Sales/ to remove it from all formulas, everything starts working again.
I have read that this could possibly be solved by unchecking the "update links to other documents" box, however next time I open the document, the box is back ticked.
Any ideas to what I'm doing wrong?