Excel - Find external links

[PMR06]

I've got a spreadsheet that upon opening says contains links to other data sources. I don't remember ever linking to other data sources though.

Does anyone know of a way to locate cells that may have such a link. The spreadsheet has 3 tabs each containing many cells of calculations so clicking thru each will be TEDIOUS.

 

[macmet]

What version of Excel are you using? I have Excel 2007 now and I can't find the links button on the ribbon, but on the other versions I believe you went to View - Links. I'm not 100% sure it was View, but go through the pull down menus.

If I remember correctly, you had to view the the links before performing any other actions. Meaning, when you first opened the file, you could view the links but if you have been working in the file already, you lose the ability. So save your work and open it again.

The other thing you could do is do a find/replace. Find one link and then copy the destination and search for it throughout the file.

Replace is one of the greatest functions in Excel if you ask me.

 

[IRstuff]

For Excel 2007, the Data ribbon has a section. second from the left, called Connections. Click the button called Edit Links and it will list the links in the document

TTFN
faq731-376

 

[chemebabak]

ctrl+f and type ".xls" to find links to other excel files. You can also do a find for "\[" if the link is a non excel file. Sometimes there is not really any link, but Excel still calls it out.

 

[PMR06]

Sorry forgot to mention Excel 2003.

I found a couple options from the help menu too, but none worked. I tried searching for [ and .xls in formulas, tried some Edit-GoTo-Special, but no luck.

The spreadsheet seems to work, it's just odd and annoying.

 

[rb1957]

if you copied charts, the data field are linked to the original s/sheet (rather than the same fields in the new s/sheet) ... thx, bill

 

[PMR06]

rb1957, good guess. There are 4 charts that were copied from other spreadsheets. I've checked all the source data values are they are all drawing data from the current spreadsheet.

I've double checked everything works. I've even opened it on a different machine off the network and it still works. Who knows?!

Thank you all for spending a few minutes thinking about it though.

 

[analogkid2digitalman]

I used this add-in once to find and delete links to a complex macro driven workbook that had 12 tabs. Somehow in the process of checking the workbook into and out of our document control system, it was riddled with a bunch of invalid links. It saved my day.

http://support.microsoft.com/default.aspx?scid=kb;en-us;188449

-AK2DM

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"It's the questions that drive us"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[sdz]

You could try ASAP Utilities which provides some tools to manage links as well as a lot of other things.