Finding external links in a workbook

[John Rampling]

I have a workbook consisting of several largish worksheets emailed to me by
a business contact. When I open it there is a warning that the workbook
contains links to cells in other files (which of course I do not have
because they are on my contact's computer not mine). Obviously I have to
select the 'do not update' option.

How do I locate the cells where these links are incorporated? The document
is too large to examine cell by cell.

BTW this is Excel 2000.

John

 

[Roadie]

Check under 'Insert --> Name --> Define whether there are ranges linked
to antoher workbook. If so, delete them or rename them.
Sometimes there are buttons or other objects linked to macros. You can
find these by selecting 'Edit --> GoTo --> Special --> Objects.
And my last option would be to check all charts. On the chart, click
right and choose Source Data.
If you still get the message, I would make a copy of the file and
delete the worksheets one by one. Save and open and onze the message
doesn't show, you have determined the sheet with the link.

 

[BrianB]

1. One way is to search for "[" which must appear in the formula of
linked sheet.
2. If you do not mind losing all formulas, convert everything to value
:-
*select all sheet tabs with Ctrl + mouseclick
*click the grey box top left on one sheet where row & column header
meet to select the whole sheet(s)
* Edit/Copy
*Edit/Paste special/Values

 

[John Rampling]

Check under 'Insert --> Name --> Define whether there are ranges linked
to antoher workbook. If so, delete them or rename them.
Sometimes there are buttons or other objects linked to macros. You can
find these by selecting 'Edit --> GoTo --> Special --> Objects.
And my last option would be to check all charts. On the chart, click
right and choose Source Data.
If you still get the message, I would make a copy of the file and
delete the worksheets one by one. Save and open and onze the message
doesn't show, you have determined the sheet with the link.

Thanks for the tips. However I do not necessarily want to delete or rename
anything - I just want to find which cell each link is contained in. And
deleting sheets one by one is no use to me - every sheet seems to have
several of these links and I still won't know what cells they are located
in.

John

 

[John Rampling]

1. One way is to search for "[" which must appear in the formula of a
linked sheet.
2. If you do not mind losing all formulas, convert everything to values
:-
*select all sheet tabs with Ctrl + mouseclick
*click the grey box top left on one sheet where row & column headers
meet to select the whole sheet(s)
* Edit/Copy
*Edit/Paste special/Values.

Thanks Brian - that worked for me.

John

 

[Don Guillett]

use this
Try FINDLINK.XLA from http://www.BMSLtd.ie/mvp

 

[Dave Peterson]

Someday, that URL will change to:
http://www.oaltd.co.uk/MVP/Default.htm