Removing/reviewing links to other workbooks

[terryc]

I have a spreadsheet which used to contain links to information in another workbook. I've now removed all these links as far as I'm aware. However, when I load it up it still says "The workbook you opened contains automatic links to information in another workbook. etc."

It's quite a complicated spreadsheet, so it's possible I've missed some of the links I was trying to remove. Is it possible to quickly review any links to other spreadsheets that might still exist within my spreadsheet, or do I literally need to click on every cell and see if I can find a link?

Thanks!

 

[MarkAndrews]

Download DominicB's add in

http://www.dom-and-lis.co.uk/

Then use Ultimate > Cells > Find & Remove Links

HTH

Mark

 

[Richard Schollar]

I often come across situations where, despite apparently all formulas (with external references) having been removed from a workbook, the update links prompt still shows on startup - ususally this is because of a defined name with external references (rather than a worksheet formula, say). So it's always worthwhile going Insert>Names>Define and reviewing your names for any culprits.

 

[terryc]

Thanks for your answers! I must admit to not really knowing what all that "define name" stuff is all about. Could you give me a quick run-through?

 

[Richard Schollar]

Sure: Say you have a range on your sheet (a good example would be a vlookup range on sheet2 A1:B100). Now, normally you'd write a vlookup on Sheet1 in the form:

=VLOOKUP(A2,Sheet2!A1:B100,2,0)

which is fine, but isn't particularly descriptive as to what is being looked up, so you could instead name the lookup range to something more meaningful (say EmployeeID, CustomerSales or whatever is appropriate). You'd do this by selecting the lookup range (sheet2!A1:B100) and going Insert>Name>Define and when the dialog box opens typeing a name into the name box. Then you can use the name in a formula like so:

=VLOOKUP(A2,CustomerSales,2,0)

assuming of course you just named your range as customer sales.

So benefits are:
1. Your vlookup formula is more meaningful (it gives you more information on its purpose - useful for anyone else and also you if you come back to this spreadsheet much later)
2. You don't have to worry about making the lookup range absolute when you copy down vlookup formulas
3. You can also make the name "dynamic" so that it will always encompass the lookup range, even if you make additions to the list or extend the number of columns etc (this requires a little more advanced stuff, but you get the idea)

Names also have other uses - Chip Pearson has an excellent article at his site (and, indeed, many other excellent articles) so check this out for more info:

http://www.cpearson.com/excel/named.htm

Hope this helps!

 

[terryc]

I couldn't get that add in to work, and it's not a problem with "define name". Does anyone have any other ideas? Thanks

 

[daniels012]

I usually go to the menu bar
Edit> Find>
then type in a bracket [
All links use a bracket. This usually does the trick.
If you need to check on more than 1 tab, select all tabs and then do the search.

Michael

 

[JazzSP8]

Worth a mention that graphs can also link to other workbooks.

If they are the culprit, click into the data series formula bar and press F9 to remove them.

 

[GlennUK]

How about Bill Manville's free FindLink add-in :-
http://www.bmsltd.ie/MVP/MVPPage.asp