Error from Excel External Reference Formula

Copper Contributor

Using Office 365, I have a worksheet where cell's contain formula's that point to information (a table of address information related to an index) held in an external workbook (in a different excel file). When opening the file containing the formula, it displays, "This workbook contains links to one or more external sources etc (buttons "Update", "Don't Update", "Help").

Selecting "Update" returns the following error :- "we can't connect to 'https://d.docs.live.net//users/Ray/SkyDrive/ etc, etc - please make sure you are using the correct WEB address.

this is followed by a further error message :-Microsoft Excel cannot access the file 'https://d.docs.live.net//users/Ray/SkyDrive/ etc, etc. There are several possible reasons.

* The file Name or path does not exist.

* The file is being used by another program.

*The Workbook that you are trying to save has the same name as a currently open workbook.

Both the original Excel File and the file containing the "Lookup" information sit on my D:Drive within a sub directory of a folder called "OneDrive".

* There is not another Workbook open (with or without the same name.

* The file is not being used by another program (as far as I am aware.

* Which leaves us with "* The file Name or path does not exist.*

As both the files reside on the dame physical drive (on my computer", I do not understand the reference to what appears to be a URL (https://d.docs.live.net//). I have tried all I know to get the formula to point to the location where the file resides,(D: Drive on my computer) but the URL always re-appears in the formula.

 

Any help appreciated.

 

6 Replies
Is OneDrive working properly on your system (does it synch)?

It appears to sync. However, when I open the same file via the WEB Interface (not from my computer) I also get an error Message "Links Disabled   Links to external workbooks are not supported and have been disabled". If I klick on the same cell that holds a formula (in my computer based version of excel), I get the following information displayed in place of the formula "This cell has an external reference that can't be shown or edited. Editing this cell will remove the external reference".

 

Thank you for your kind assistance.

That is by design, Excel on-line is not capable of calculating links to external files. They should work when opened on your desktop Excel though.

Thank you for coming back to me. 

Yes, my perception is that it should work when I open it on my desktop Excel but that is my problem, it doesn't and I get the error messages.

When I contact Office 365 support, they tell me that it is a OneDrive problem, when I have tried to get to the bottom of the problem via OneDrive Support, I am told that it is an Office 365 problem.

Could anyone help to steer me to location where I might get some support to get to the bottom of the problem.

Thank you

I just tested this on my own OneDrive. If I refer from one workbook to another, save both files and then open the SOURCE workbook and change the linked cell, exit and then open the TARGET file, the new value is correctly displayed. Even so, if I open the Edit links dialog and click the Check status button, Excel warns me that values to other workbooks were not updated. Even though they were apparently updated as I can see the new value.

Looks like this doesn't work as it should. I'll try to make the Excel team aware of this issue.

I played both with ODFB and OneDrive Personal and was not able to reproduce - links are updating and Edit Links shows OK status.

 

However, it looks like that or similar issue exists for about 3 years https://onedrive.uservoice.com/forums/913528-onedrive-on-the-web/suggestions/7178317-add-option-to-a...

 

Perhaps depends on OneDrive client version, not sure. Anyway, my one 18.192.0920.0015.

 

And I didn't play with symlinks if it changes something, it looks like they could help with old client (groove.exe)

https://support.office.com/en-gb/article/can-t-synchronize-onedrive-for-business-files-and-folders-f...