[[email protected]]

On Tuesday, December 7, 2010 at 8:38:07 AM UTC-8, Dave Orth wrote:
I had the dreaded "This workbook contains links to other data sources" but was completely unable to locate the offending cell. It turned out to be an errant "Data Validation" cell.

Using Excel 2010, I was able to see that I had a link to an external file.. Using "Data - Edit Links" it showed the file, but using the "Break Link" or "Change Source..." did not affect it. This was maddening as Excel doesn't give information on what cell is using this link.

I used the "File" menu and selected the "Check for Issues - Check Compatibility" menu which informed me that "One or more cells in this workbook contain data validation rules which refer to values on other worksheets." This is fine in Excel 2010, but not compatible with previous versions.

Examining the results from this report lead me to a (large) range of cells to examine, but far less than looking through the whole spreadsheet. I clicked the "Data - Data Validation - Circle Invalid Data" to identify cells with non-compliant data. Scanning for a red circle, I found one which had a "Source" for data validation which pointed to the external "link" which I had been trying to delete.

Once I corrected the Source for the data validation, my issue was resolved. This was a fairly large spreadsheet and I was a bit lucky that I only had to scan a few hundred cells. If this had been a much larger spreadsheet with more Data Validation, it would have been nearly impossible using this method.

Good luck!

Dave O

On Monday, April 12, 2010 8:45 AM swoxo wrote:

In Excel 2007, I have a workbook with one link that I want to break. In the
Ribbon, I go to the Data tab, and in the Connections section I choose Edit
Links. In the Edit Links window, I select the link that I want to break
(there is only one link there), and I click on the button that says "Break
Link". I get a pop-up warning that once I break the link, my action cannot
be undone. There are two buttons under the warning, one that says "Break
Links" and one that says "Cancel". I click on "Break Links" and nothing
happens. My link is still there, and if I save and close the workbook, I
still get the security alert every time I reopen it.

I cannot delete or relocate the source Excel workbook that mine is linked
to, because other people are using that source. I just want to break my
workbook's link to that source. Any ideas why the Break Link button is not
doing that for me?


On Monday, April 12, 2010 9:06 AM Don Guillett wrote:

Not sure about this but you may? have to UN share first.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software



On Monday, April 12, 2010 10:08 AM swoxo wrote:

That is a good suggestion so I gave it a try, but it turned out that the
workbook was not shared, I already had exclusive use.

Thx,
swoxo

"Don Guillett" wrote:


On Monday, April 12, 2010 10:20 AM Dave Peterson wrote:

I do not use xl2007 enough to have seen this problem...

But I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

swoxo wrote:

--

Dave Peterson


On Friday, August 20, 2010 10:45 AM Terry Morrison wrote:

Hi, I have found two scenerios recently in which links could not be broken.

1) a control button or dropdown box was copied in from another workbook and the link was part of the control properties. It can't be broken from the connections button, has to be deleted within the control dialog box

2) a graph with data source pointing somewhere else seems to have the same issue.

Hope this helps, I was about to start over with the workbook and had this idea. It fixed the problem.


Submitted via EggHeadCafe
Microsoft LINQ Query Samples For Beginners
http://www.eggheadcafe.com/training-...Operators.aspx

I had this problem, which persisted even after removing formulas that linked to an external source. Using your method led me to a group of cells with blank dropdown boxes that were somehow futzing up my workbook. Removing the dropdown boxes fixed the issue. Thank you so much!! Five years later. :)