797 votesAmby 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.
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.Amby supported this idea ·