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
79 comments
-
Anonymous
commented
And yet, it works perfectly well with Dropbox. The irony.
-
j
commented
I fixed mine. I had issues with sumif( ) type functions looking in other files.
It takes a while but you only have to do this once.
Make sure all the connected files are in OneDrive.
Use the GetData wizard to import the data you are linking to for your calculations into the same spreadsheet as the formula. default behaviour is to dump the data into a new sheet as a Table. Rename the Table and the worksheet. add some text explaining where this random data comes from. because good practice.
Go to the formula that used to reference the data directly in the other worksheet (which now doesn't work) and point it to the new Table.So long as the other workbooks aren't open, to get fresh data, just hit Data/RefreshAll.
-
Doug Scott
commented
I can confirm that if the workbooks are in the same folder the links will update fine but if they are in different folders they will not and you get a warning when you open the workbook that the links will not update.
-
Jason Brad
commented
It has been four years past, NOTHING changed, what happend to Microsoft?
-
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..
