How to find out unvalid hyperlink formula?

[smart.daisy]

I have a big file with several worksheets. All worksheets used formula
linking to other files. When I hit update link, it tells me that some links
are unvalid. How can I check which link is actually unvalid? I use excel2000.
Thanks.

 

[David McRitchie]

They aren't necessarily hyperlinks. If you refer to a cell in a particular
sheet in another workbook or even the same workbook, and you delete
that sheet the link to that cell is broken and you would see #REF! that
has nothing to do with a hyperlink -- although you could have cells with
invalid references that are also hyperlinks.

To help you find the problems download findlink by Bill Manville in
http://www.oaltd.co.uk/MVP/Default.htm

 

[smart.daisy]

Sorry, my company doesn't allow download .ZIP in company's computer.

They are formula picking up data from other files. When I move the file with
these formula to another folder, the formula change automatically. They use a
different path. Then when I open this file with formula, EXCEL pop up a
dialog to ask me updated link. Then EXCEL remind me that it can't find
certain files these formula pointing to. I manually updated some formula
already. But it still has some link to the old path. How can I check which
formula is still pointing to invalid/old path? Thanks.

They aren't necessarily hyperlinks. If you refer to a cell in a particular
sheet in another workbook or even the same workbook, and you delete
that sheet the link to that cell is broken and you would see #REF! that
has nothing to do with a hyperlink -- although you could have cells with
invalid references that are also hyperlinks.

To help you find the problems download findlink by Bill Manville in
http://www.oaltd.co.uk/MVP/Default.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I have a big file with several worksheets. All worksheets used formula
linking to other files. When I hit update link, it tells me that some links
are unvalid. How can I check which link is actually unvalid? I use excel2000.
Thanks.

 

[David McRitchie]

If you copy worksheets individually to another workbook, it is best
to group the sheets related to one another and copy or move them
as group.

When you move entire files around, you want to do so with Excel down
so Excel does not get involved, but it still often attaches the pathname.
Excel can only have one workbook
with the same name open at a time, so you can end up with the wrong
pathname.

Anyway Bill Manville's FindLink will identify any string that you want
to look for and make it easy to correct them if you know what you want.

Check with your Help desk, or your Network group to find out what you
are allowed to do, you have a problem and from what you've described
I think the FindLink addin would be of assistance. Whether it limitation
is only on zip files or includes restrictions on who can install anything
they can help you.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Sorry, my company doesn't allow download .ZIP in company's computer.

They are formula picking up data from other files. When I move the file with
these formula to another folder, the formula change automatically. They use a
different path. Then when I open this file with formula, EXCEL pop up a
dialog to ask me updated link. Then EXCEL remind me that it can't find
certain files these formula pointing to. I manually updated some formula
already. But it still has some link to the old path. How can I check which
formula is still pointing to invalid/old path? Thanks.

They aren't necessarily hyperlinks. If you refer to a cell in a particular
sheet in another workbook or even the same workbook, and you delete
that sheet the link to that cell is broken and you would see #REF! that
has nothing to do with a hyperlink -- although you could have cells with
invalid references that are also hyperlinks.

To help you find the problems download findlink by Bill Manville in
http://www.oaltd.co.uk/MVP/Default.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I have a big file with several worksheets. All worksheets used formula
linking to other files. When I hit update link, it tells me that some links
are unvalid. How can I check which link is actually unvalid? I use excel2000.
Thanks.