MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by yakut on December 27, 2001 6:51 AM

Does anyone know how can I locate links in my spewadsheets (EXCEL 2000)???

Posted by JAF on December 27, 2001 7:44 AM

From the Edit menu, select Links to list the "real" links in your spreadsheet.

If the Links menu is ghosted but the workbook still says it contains links when you open it, then you've got "phantom links" which can be a bit of a bugger to trace.

Phantom links are usually caused by named ranges which refer to another workbook, but they can also be caused by VBA code or charts referencing another file.

Microsoft have an add-in called Delete Links (or something similar) that you can download. This walks you through a Wizard to get rid of these phantom links.

Posted by yakut on December 27, 2001 11:46 PM

True but ???

:That is true, but what I want is which cells are linked. If I go to Edit, Links it shows me the files that are linked but not which sells in the workbook are linked to that file. That is what i am trying to find...

Thxs for all your help

Posted by Adam S. on December 28, 2001 10:35 AM

A couple ways


So you got some links you're trying to find in your (possibly massive) spreadsheet. I recommend first checking:

1: Insert\Name\Define

Sometimes named ranges act as links after copying to a new file. If you use named ranges, glance these over just to confirm the source data isn't actually from another (usually old) workbook.

2: Same applies to Charts/Graphs.

If you have any, glace over any chart series you have just in case (again) that the source data for these charts come from another file.

3: Locating links in your Formulation (probably what you were going for in the first place). A couple methods can be useful.

a. Tools\Options\<View tab>\Forumulas
Will let you view a sheets formulas. If you only have your current file open, links should be pretty obvious as formulas with links will include the file path.

b. Similar to above, you can do an Edit\Find for part of the links path or filename to search for individual links one at a time (Ctrl+F "C:\My Documents\MyMonthlydata\2001\December" would be more than enough search criteria for example).

Hope that helps somewhat

Adam S.