Linked Excel Files in Sharepoint don't work when links update.

%3CLINGO-SUB%20id%3D%22lingo-sub-1638101%22%20slang%3D%22en-US%22%3ELinked%20Excel%20Files%20in%20Sharepoint%20don't%20work%20when%20links%20update.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638101%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20excel%20files%20with%20links%20to%20each%20other%2C%20both%20saved%20in%20the%20same%20folder%20in%20OneDrive.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20excerpt%20of%20one%20of%20the%20formulas%20when%20I%20first%20created%20the%20links%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E...SEARCH(%22IVC%22%2CINDEX('%5BLive%20Pipeline%20(v2.00).xlsm%5DProject%20View'!%24A%241%3A%24FZ%2450...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20after%20closing%20and%20opening%20the%20file%2C%20they%20all%20changed%20automatically%20to%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESEARCH(%22IVC%22%2CINDEX('%3CA%20href%3D%22https%3A%2F%2FMYORG.sharepoint.com%2FShared%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FMYORG.sharepoint.com%2FShared%3C%2FA%3E%20Documents%2FSales%2F%5BLive%20Pipeline%20(v2.00).xlsm%5DProject%20View'!%24A%241%3A%24FZ%2450%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20changed%20so%20the%20value%20they%20return%20is%20%23VALUE%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20do%20a%20Find%2FReplace%20on%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2FMYORG.sharepoint.com%2FShared%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FMYORG.sharepoint.com%2FShared%3C%2FA%3E%20Documents%2FSales%2F%20to%20remove%20it%20from%20all%20formulas%2C%20everything%20starts%20working%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20read%20that%20this%20could%20possibly%20be%20solved%20by%20unchecking%20the%20%22update%20links%20to%20other%20documents%22%20box%2C%20however%20next%20time%20I%20open%20the%20document%2C%20the%20box%20is%20back%20ticked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20to%20what%20I'm%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1638101%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%20Office%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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?

 

 

0 Replies