Finding external links in a workbook

  • Thread starter Thread starter John Rampling
  • Start date Start date
J

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
 
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.
 
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
 
Roadie said:
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.
Click to expand...

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
 
BrianB said:
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.
Click to expand...

Thanks Brian - that worked for me.

John
 
Back
Top