Make it easier to find external links
Not just break them - help us to find where they are. It's time consuming to look in charts, data validation, conditional formatting, data and shapes.
Thanks for your feedback! We’re reviewing your suggestion. Remember, the more votes a suggestion gets, the more likely it is that we’ll do it.
How the **** does this idea not have the most votes????
Is it because this should really just be considered and viewed as a bug???
@Roy: P.S. I have tried your suggested add-ons but they are all blocked for me just as I thought they would be.
MS: Again, this is not a feature request ; this is an ask to FIX this ridiculous stupidity. Why the **** would it ever be considered intended behaviour for an error advising something is broken to not also include the specifics about what IS BROKEN?????!!!!!!
MS: Also, I hate you for being the only place/area/etc that entices me to use multiple question marks, exclamation marks, and CAPS lock! Truly from the depths of my soul, F-U
@Roy : wow! Thank you so much for your detailed and helpful response!
I presume that the reason that I have not stumbled across your suggested work arounds is because my employer tends to block almost all add-ins that enable "non-standard" features. And rightfully so.
Fundamentally though, should it really be so difficult and unintuitive?
MS : You really need to get your sh*t together. It is absolutely ridiculous that this has been an issue for more than a decade!
Again, thank you so much @Roy for spending your time trying to help me!
@mark: I would appreciate you so much if you could let me know how to use the "Find" formula that you write of to locate the broken links! Please?! Thank you!
Gary Rosner commented
About how many votes does the Microsoft team require before addressing this issue???
The issue remains unaddressed because the following message provides no actual direction as to where the errors may reside.
Excel found a problm with one or more formula references in this worksheet. Check that the cell references, range names, defined names and links to other workbooks in your formulas are all correct.
Ivan Bondarenko commented
This is a great idea!
There are votes also for similar thread,
"Excel found a problem with one or more formula references" - Please tell me where!
This is key for migration. When migrating thousands of files from a local drive to SharePoint, it would be incredibly beneficial to have the ability to identify files that have external links so that the links can be reestablished in the new environment. This is currently prohibiting our file migration and user adoption of O365
Jerry Norbury commented
There's even more places they can hide - conditional formatting and inside Tables. Neither of these is particularly visible.
The formulas in Tables feature is particularly horrible because it is completely hidden and can differ from the formula visible in the cells...thus still point at an external link even after you've changed ALL the cells in a column to point somewhere else. Nightmare.
Miguel Ulloa commented
The issue is that if the external reference is in an exotic place, like a validation list or named reference, there's no way of finding out other than going through cell references and visually inspecting everything to make sure. The "edit links" menu should be updated to display every place where external references can exist.
Inquire Add In is not enough. I can see what the linked workbook names are, but I cannot see how they are linked and am therefore in no position to fix them. I have tried editing the link in Inquire but that has only "stuck" about 1/3 of the time in one workbook, and no times in another workbook that I am fixing.
Please Please Please provide a simple answer to this. I have often found problems due to external links I never knew I had. In my work setup I can't put some important files in sharepoint because external links prevent them being opened and I just can't find them.
Finding and fixing external links (i.e. links to other workbook) is almost a Sherlock Holmes investigation, namely because these may be "hidden" in data validations or conditional formatting, This may happen as a result of a simple copy/paste sequence between workbooks.
Why not adding an "External links" option to the Find & Select menu , which should identify the cells searching in formulas, but also in validations, formatting, defined names etc..
Gerdami Des Betes commented
Wow, it is already under review ... since 24 September 2015!
Really need this feature. I just okayed copying 25 "links" that matched formulas in a spreadsheet. The difficulty? When I went looking for the links using the text given in the error messages they didn't actually exist. The single cell with a link to a different workbook that caused the problem, I inherited from another user.
Although it's not super fast, this can be done using the Inquire add-in
For me this is one of the most annoying limitations because we know that the application knows exactly where that external link is (otherwise it can't break it), but there is no button for us to see where the link is. We can work around it by doing a Find in formulas, but a shortcut from the "Edit links..." screen would make a LOT of sense.
balakrishna sappa commented
yes, finding external references in conditional formatting rules is the most tricky/difficult one.
Col Delane commented
This is definitely required - and shouldn't need a pile of votes to prompt MS to fix it, for it just makes sense that it should be done!
Whilst they're at it, they should also fix one of the major causes of this problem, being the replication in the target workbook of EVERY Defined Name in a source workbook when one or more of the latter's sheets (whether or not there are any Sheet-scoped Names, or formulas which include Names elsewhere in the workbook, on the target sheets) are moved or copied from source workbook to target workbook. This could be avoided if during the Move/Copy Sheet process:
1. Excel identified if there are any Names referencing cells on the target sheets or if there are any formulas on the target sheets that reference Names elsewhere in the workbook, and
2. users were given the option to replicate all Names, only user-selected Names, or no Names.
I live in hope but with little expectation.