Add option to avoid OneDrive from changing Excel references to other files
Everytime you link an Excel workbook to other workbook, OneDrive changes the reference to the file, making reference to an online files. However, if one the Owrkbooks are in differente folders inside OneDrive, the link will break, even if you try to open the file in the computer containing them. That is a HUGE problem
Anonymous
shared this idea
75 comments
-
Aaron Moayed commented
SOLUTION (Doug Scott found it):
"I no longer have a problem with changing links since I unchecked "Use office 2016 to sync Office files that I open" on the Office tab in the One Drive Settings."I tried the rest of the offered solutions here, and his was the only one that worked!
-
Bartłomiej Michna
commented
This also prevents the auto-refreshing of external data references in Powerpoint. The application doesn't even mention that will be a case, but it's happening in Office 365 now.
-
Brad Wilson
commented
I use Excel a lot for my business, which at times involves working on some complex business modelling and a lot of collaboration with external parties.
When I receive a spreadsheet that has had several hundred hours of work spent on it by some highly paid people, and simply by opening it I cause all of the external references to get replaced by an online URL to my specific onedrive account for no good reason and without me asking it to do so, those highly paid people and their bosses and their clients get really mad.
This brain-**** of a feature has already resulted in god-knows how many thousands of dollars of wastage because I was the guy who opened it from Onedrive (more than once, I'm embarrased to admit), so whatever collaborative benefits it was designed to deliver is, in my case at least, utterly negated by the impacts of some very common real-world scenarios that must occur in offices every day.
The only way around this is to disable the Onedrive option "Use Office applications to sync Office files that I open", and be sure to only ever open Excel files locally. This is unfortunate, because this option also applies to Word and Powerpoint files too, and ultimately means that the otherwise great collaboration features of Office go unused; all because of one silly weakness in just one part of the Office suite.
Microsoft, you do some good stuff these days, but it's nuanced issues like this that have really big impacts and weaken the rest of your products (there are many other issues of a similar nature) that I just shake my head at in disbelief - more so when it has been a known and ongoing issue for a number of years.
-
Douglas Scott
commented
Are you syncing OneDrive with your computer so that it gets the data off of your computer and not the cloud?
-
Carlos Solano
commented
Have this problem. It is impossible to work in collaboration between people and files with this problem. Every time I refresh data in my files and open it the link is broken and I have to make again all the vlookups, is a waste of time
-
Anonymous
commented
I have this problem in 2019 with Excel add-ins changing their link paths between computers I’ve OneDrive. Very frustrating.
-
ANTONIO LUIS JAMAS
commented
I 100% agree with "Anonymous commented · September 23, 2018, 11:53 AM" below and considering that it was posted on Sept 18 and I still have the same problems, that means MS doesn't care about clients. I'm still using Dropbox and probably I will keep using that for a long time...
-
Doug Scott
commented
I have office 365 and have not had this problem for over 2 years.
-
Ricky Tor
commented
Any one know if this problem continues in Excel 2019?
-
Doug Scott
commented
I have work books that link to other workbooks and do not have this problem. just make sure you sync your OneDrive with your computer and open the workbooks on your computer.
-
Anonymous
commented
To Samm:
Thanks, your solution works but only if we do not update the links later... The problem remains and Microsoft is very disappointing -
Amby
commented
Hey Guys, I spent a few hours on this and realize lots of people have this problem. I got mine fixed. I am on Mac running o365.
The problem was that when I open the file, excel complains a broken link points to a OneDrive path that seems to be invalid as it looks like my local path. Not sure how I got to that. All I can think of is may be because I have been working on local files and Save As to OneDrive to backup my working versions.
So, back to the solution, I first tried to do a Edit->Find on link name on all formulas, content, etc.. Didn't find anything.
I then create a new blank workbook, from the original workbook, I select all sheets from sheets tab, do a Move / Copy..., and select the blank work book. All sheets copied over.
I then save and reopen the new workbook. Found out the problems stick. I then went ahead and delete one sheet at a time. Each time, I save, close, and re-open to see when the problem goes away.
I finally got to one sheet that caused the problem.
After spending some time, I find out the problem is related to Data Validation. I have cells that use Data Validation to create a drop down list. I guess it must have pointed to remote file. Unfortunately Data Validation does not get searched as formulas.
I clear the cells, problem went away. I recreate the data validation again. Happy Day for me!
Lesson learn, I think if a link is created in features that's not in a formula, find the broken link is impossible. One can only narrow down to which sheet and then check Data Validation, Conditional Formatting, or any feature that can link to other sheets.
Hope that helps.
Amby.
PS. Man, it's been 10 years since last time I used a spreadsheet. Gotta say, o365 makes me feel like it has only advanced 1 years compares to other solution out there that are growing a light speed. I think that's what happens when too much comfortable passive revenue from the existing user base makes business lazy to continuously improve.
-
Doug Scott
commented
I no longer have this problem since I started syncing One Drive to my local hard drive.
-
Anonymous
commented
This is a really annoying issue. Excel keeps on referencing an external URL but the rest of the URL is a location on my local drive. No way of removing it so I have to manually update every time. Please fix or I will go back to Box.com
-
Nick
commented
This is still a big problem on O365 Mac Excel. When you re-open a file with links to a local source file, the links are automatically changed to a long hyperlinked file address. Excel says it can't find the file, then I have to go re-point the Data Source from Remote File to Local File. Very annoying and no options to fix on a mac.
-
Samm
commented
Go to File > Options > Advanced > At general section select Web Options > Select Files tab and untick "Update links on save'.
However the formula that file path had already change, will have to change back manually.. if only i found out this before my formula changed to file path..
-
Steve
commented
Hello,
i tried two things: write 'file:///' before the address in the 'Insert Hyperlink'-popup. And another thing helped: Menu Data -> Edit Links -> Button 'Startup Prompt...' -> choose the middle option 'Don't display the alert and don't update automatic links'
Both together worked at me (may be one would have been enough?) with Windows 10. -
Yiannis St
commented
The "solution"to unlink the Onedrive folder from default C:\Users\xxxx\OneDrive and link it to C:\OneDrive to all computers, does the work of solving the problem with broken links inside excel files.
But the problem is when someone of the colleague uses an Apple PC. In Apple PCs there is no C:\ path. So does anyone knows who to solve this problem on an Apple MacBook f.e?
-
Rick of course
commented
There doesn't seem to be a way of hyperlinking to specific cells (and presumably also words or sentences in Word) in Excel on Onedrive. There also doesn't seem to have the ability to have formulas linking and amending data between 2 or more Excels. IE. updating information on Excel 1 in certain cells, in certain ways, leads to a 1 of a variety of permutation results in Excel 2. This seems a severe oversight / issue.
I've not checked every possible action, but haven't found a solution to this, so assuming i'm correct, I'll get back to the slow, sarcastic clapping I save for other Microsoft "updates" and "improvements".
-
Anonymous
commented
Microsoft is late to the game in cloud storage. Dropbox has a much better, more seamless integration to both Windows and Mac operating systems. To differentiate, OneDrive must offer something that Dropbox cannot. For us the ability to edit a Word, Excel, or PowerPoint file across time zones, locations and multiple users simultaneously is that feature. However, the update of file edits are still too slow, particularly when using the full featured desktop version of Office. The Cloud features of Word, Excel, PowerPoint are impressive given the cloud implementation, and updates are faster in this modality, but there are too many features lacking in the cloud / saas version to replace desktop functionality. Therefore in order of priority we need:
1. Significant speed improvements to match Dropbox on file updates and the synch of those updates to all users editing an Office file.
2. Better OS integration wtih files and folders similar to Dropbox (today we used only Dropbox for read-only viewing of files between teams, and selectively used OneDrive only when there is a need for team editing).
3. Over time, build better cloud / saas versions of Office to enable not only faster updates but to stay ahead of Google Docs. Today Google Docs are useful barebones tools but cannot nearly match Office features. That will change if Microsoft does not agressively upgrade cloud based functionality.
4. We'd like to see a roadmap of this feature set built into published material, not just for MSFTs largest enterprise clients but for small-medium businesses who in total probably amount to as many ore more users than those in the enterprise. We all watch what MSFT, Google and others do and daily vote with our dollars and usage of collaborative productivity tools. MSFT needs to be more transparent about timing relative to the competition.
