How do I Find links (find linked cells)?

[AudreyCynic]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I've just started reviewing a workbook given to me by one of my clients, and it gives the standard "can't find links" message when the file opens. I'd like to see which cells in the worksheets (there are about 50 worksheets in this workbook) are using these links. I can't find any way to do this in Excel 2008. Has anyone got a solution that works with this version of Excel 2008 for Macintosh? Thanks

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I've just started reviewing a workbook given to me by one of my clients, and
it gives the standard "can't find links" message when the file opens. I'd like
to see which cells in the worksheets (there are about 50 worksheets in this
workbook) are using these links. I can't find any way to do this in Excel
2008. Has anyone got a solution that works with this version of Excel 2008 for
Macintosh? Thanks

Have you looked in the defined names? What does the edit links dialog show?
From here you can get the file names and then do a search for that name(s)
on the worksheets.

 

[Audrey Kiehtreiber]

Hi Bob,
Yes, I did use the Edit Links to see what the name of the external workbooks were, and I tried a search but it didn't come up with anything. I had read in a previous posting that other people have tried this method and it didn't work for them either. They suggested using a tool .... but when I went to look for it there was no indication if it worked in the 2008 version of Excel so I am hesitant to try it until I hear back from the developer. Here is a quote from a posting titled "Finding Pesky Links"

"For hard to find links, download and install Bill Manville's FindLink
add-in:

<http://www.bmsltd.co.uk/MVP/Default.htm>"

Do I need to search for the exact filename or can I just find using a partial file name. The file names are long and weird... so I just used a single word from them like REPORT to search. Someone else suggested using "[" but that seemed to return nothing either. Suggestions?

My feeling at this time is that there ARE no cells actually linked... but before I break the links I'd like to verify this assumption. Does Excel hold on to the link reference even when the cells have been deleted?

 

[Bob Greenblatt]

Hi Bob,
Yes, I did use the Edit Links to see what the name of the external workbooks
were, and I tried a search but it didn't come up with anything. I had read in
a previous posting that other people have tried this method and it didn't work
for them either. They suggested using a tool .... but when I went to look for
it there was no indication if it worked in the 2008 version of Excel so I am
hesitant to try it until I hear back from the developer. Here is a quote from
a posting titled "Finding Pesky Links"

"For hard to find links, download and install Bill Manville's FindLink
add-in:

<http://www.bmsltd.co.uk/MVP/Default.htm>"

Do I need to search for the exact filename or can I just find using a partial
file name. The file names are long and weird... so I just used a single word
from them like REPORT to search. Someone else suggested using "[" but that
seemed to return nothing either. Suggestions?

My feeling at this time is that there ARE no cells actually linked... but
before I break the links I'd like to verify this assumption. Does Excel hold
on to the link reference even when the cells have been deleted?

Bill Manville's tool will NOT work with Excel 2008 because it requires VBA.
You should be able to search for a partial name, and using [ is a good idea.
Just make sure that "[xxxxx" in the search is really the beginning of the
file name. did the file originate on Windows? Are there buttons or macros
attached to objects? These can also cause link references.

If the search show nothing, then break the links, save the file as a backup
until you are sure there is no other damage.

 

[Audrey Kiehtreiber]

Hi Bob,

I did the search again using the suggested parameters, and still didn't find anything. I searched for "[Filename_First_Word".

I began a manual search by switching between worksheets, and at one point an alert notified my that the sheet I was on had a link. I then clicked on each object to manually see what the cell references were, and finally clicked on a Chart and found that it was what had the external reference.

Again, the previous search didn't bring it up, even though the chart was using external references to files I had included in my search attempts.

There are 68 worksheets in this workbook, is there NO way to find out which cells/Charts are using external links? I find it surprising and disappointing that there is no mechanism for dealing with this problem.

I appreciate your help on this. Any suggestions?

 

[Bob Greenblatt]

Hi Bob,

I did the search again using the suggested parameters, and still didn't find
anything. I searched for "[Filename_First_Word".

I began a manual search by switching between worksheets, and at one point an
alert notified my that the sheet I was on had a link. I then clicked on each
object to manually see what the cell references were, and finally clicked on a
Chart and found that it was what had the external reference.

Again, the previous search didn't bring it up, even though the chart was using
external references to files I had included in my search attempts.

There are 68 worksheets in this workbook, is there NO way to find out which
cells/Charts are using external links? I find it surprising and disappointing
that there is no mechanism for dealing with this problem.

I appreciate your help on this. Any suggestions?

Without macros, I'm afraid that it will be difficult. The series formulas in
a chart is not "FINDable". Why do you need to find the links? To change
them, you can use the change Link. If you want to remove them, use Break
Link.

 

[Audrey Kiehtreiber]

..... is there NO way to find out which

Without macros, I'm afraid that it will be difficult. The series formulas in
a chart is not "FINDable". Why do you need to find the links? To change
them, you can use the change Link. If you want to remove them, use Break
Link.

I need to find them because I need to know what I am changing. I have no idea when I "break" a connection what I am breaking. Basically, how do I know which of the 200 charts I've just broken? Go through and try a visual inspection to see if I can figure out which chart no longer has a series attached to it? This will take me forever! It's not obvious, some of the charts have empty series fields which are being filled in as time goes on, so it's not really obvious which are empty on purpose and which are actually getting data from external sources.

I really appreciate your responses, but I was hoping that more than just one person was looking at this board and might have some ideas. I can try and post a request to the Microsoft features page for future revisions, but frankly a future fix will not solve my immediate problem.

Thanks anyway, I appreciate all your suggestions.

 

[Bob Greenblatt]

I need to find them because I need to know what I am changing. I have no idea
when I "break" a connection what I am breaking. Basically, how do I know which
of the 200 charts I've just broken? Go through and try a visual inspection to
see if I can figure out which chart no longer has a series attached to it?
This will take me forever! It's not obvious, some of the charts have empty
series fields which are being filled in as time goes on, so it's not really
obvious which are empty on purpose and which are actually getting data from
external sources.

I really appreciate your responses, but I was hoping that more than just one
person was looking at this board and might have some ideas. I can try and post
a request to the Microsoft features page for future revisions, but frankly a
future fix will not solve my immediate problem.

Thanks anyway, I appreciate all your suggestions.

I think a lot of other people are also looking at these posts. They just are
not able to offer any other suggestions. As I said, without macros, there is
not any choice but to do it manually. If you have access to a windows
machine, you may try Bill Manville's Find Link macro there.