break links not working

[swoxo]

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 isn't
doing that for me?

 

[Don Guillett]

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

 

[swoxo]

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

Thx,
swoxo

 

[Dave Peterson]

I don't 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

 

[Terry Morrison]

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.

 

[smash_626]

You may please try the following:

Undder the formula bar, click name manager and delete the names and then give try.
I hope it will work.

 

[clarkscott91]

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 isn't
doing that for me?

One scenario that leads to hard-to-find links is the use of cross-sheet conditional formating. If cells containing cross-sheet conditional formating are pasted into a new file, a link is created within the conditional formatting back to the originating file. This link cannot be broken in the traditional way. You have to change or clear the conditional formating to remove the link. To clear, go to the Home tab on the ribbon, click Conditional Formatting-->Clear Rules-->Clear Rules from Entire Sheet. To find the cell ranges containing this type of formatting across an entire workbook use File-->Check for Issues-->Check Compatibility. Within a sheet you can also useConditional Formatting-->Manage Rules.

 

[aaron.olesen]

Do you have any drop down boxes in your workbook. Often it is the case that the data validation is referencing the outside workbook. Check those and resource if necessary.

 

[vpfinance]

Summary of all of the answers in this thread:

Links can be from cell in one workbook to another in another workbook, which is easy to find, but there are OTHER ways that workbooks can be linked...and ones where it is impossible to break the link via the Modify Links button under the Data menu. These are:

1. Conditional formatting (home menu) can refer to another workbook (suggestion: kill your conditional formatting and redo it line by line);

2. Data validation (drop-down boxes) citing a list from a cell range in another workbook, or anything like this in data-validation (see Data menu);

3. Names: in Excel you can "name" a range of cells, and refer to that name just like any other cell... so a formula like =A1+A2 could also be =C5+WALLSANDWINDOWS. These names can reference cell ranges in another workbook,so you should look at them.


Two tricks to finding out what's wrong:

1. Under the Data menu, use the option "Circle invalid data" (under the Data Validation button);
2. Under the File menu (or Home Ribbon in 2003), validate the Compatibility- it won't tell you what is causing the problem exactly, but it will give you a good idea of where to look.

Good luck!

 

[jlysenko]

You may please try the following:



Undder the formula bar, click name manager and delete the names and then give try.

I hope it will work.

Thanks for the tip -- I tried delete names and the links went away!

 

[ryanallenkirk]

You guys have been really helpful, I was able to figure out a 4500+ occurrence problem within a workbook containing 100+ sheets

1) File -> check compatibility -> paste into new sheet

2) Formulas ribbon -> defined names region, select use in formula -> paste names -> paste list.

3) If this is what is causing your problem, you will now see see all of these names along with their locations. Now, select all tabs (ctrl + click) and go to home -> general -> number -> format as raw text

4) Now search workbook for these names and either replace them or delete them.

I hope this helps,

rk

 

[ryanallenkirk]

3) B. Using the name manager could also be really helpful depending upon what is causing your defined name error.

 

[mwferg]

First time using this Google groups, can't believe this problem is this old.. I have a spreadsheet that references a named range on another spreadsheet. I use the data validation to save entries to one page. Once completed,I copy/move the ss I made my entries in and save as a new file. Each timethe spreadsheet is opened it prompts to update, was never able to get break links to work (Excel 2010). Tried the Clear All for Data Validation, Ctrl+F3 to delete names, etc, but was never able to break the link. Every time I opened the file I got the update prompt.

Until I saved the file as Excel 97 - 2003, now when I open the file, no prompt to update the link I could never break. Thank you MS for leaving this problem in existence for so long.

 

[rohit.s.09]

You may please try the following: Undder the formula bar, click name manager and delete the names and then give try. I hope it will work.

Thanks alot dude...
I searched every place and the link was lying in one of the names....
Problem solved..

 

[jcoanda]

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 spreadsheetwith more Data Validation, it would have been nearly impossible using thismethod.

Good luck!

Dave O

This worked for me. Thanks!

 

[phuerta]

You may please try the following:



Undder the formula bar, click name manager and delete the names and then give try.

I hope it will work.

That worked!!!! thanks so much!!!!

 

[pjay1437]

I would never have figured out my issue without this suggestion. Thanks!

 

[gvsgrewal]

One scenario that leads to hard-to-find links is the use of cross-sheet conditional formating. If cells containing cross-sheet conditional formating are pasted into a new file, a link is created within the conditional formatting back to the originating file. This link cannot be broken in the traditional way. You have to change or clear the conditional formating to remove the link. To clear, go to the Home tab on the ribbon, click ConditionalFormatting-->Clear Rules-->Clear Rules from Entire Sheet. To find the cellranges containing this type of formatting across an entire workbook use File-->Check for Issues-->Check Compatibility. Within a sheet you can also use Conditional Formatting-->Manage Rules.

Hi - this suggestion was very helpful as I was having the exact issue aftercopy-pasting information from one workbook into another. Thank you!

 

[mjhogan2000]

Thanks for the tips - the solution for me was checking out the invalid data left behind by using drop down boxes.

Mark

 

[cmckee20]

This worked for me! I had a bunch of "rogue" named ranges in here. Thank you!