Removing/reviewing links to other workbooks

terryc

Board Regular
Joined
Jun 27, 2006
Messages
66
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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Jun 27, 2006
Messages
66
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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:

Code:
=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:

Code:
=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

Board Regular
Joined
Jun 27, 2006
Messages
66
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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

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

Well-known Member
Joined
Sep 30, 2005
Messages
1,218
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.

:)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,407
Messages
5,547,766
Members
410,811
Latest member
adustin42
Top