Range name find and replace

B

bobclark

I am repairing a repetitive worksheet where only half of the range names were
recovered. When I copied from another sheet the non-existent range names copied
over as links i.e. 'c:\directory\[copied from workbook.xls]sheet'!cells and i
want to rename them (in bulk) to 'sheet'!cells. As half of the range names are
correct, how (if I can) do I do this without getting cramps with
insert|name|define and correcting every entry individually? or can it be done?

TIA HTH

Bob Clark
 
I

IC

I am repairing a repetitive worksheet where only half of the range names
were
recovered. When I copied from another sheet the non-existent range names
copied
over as links i.e. 'c:\directory\[copied from workbook.xls]sheet'!cells and
i
want to rename them (in bulk) to 'sheet'!cells. As half of the range names
are
correct, how (if I can) do I do this without getting cramps with
insert|name|define and correcting every entry individually? or can it be
done?

Have you tried Excel's Replace facility to replace 'c:\directory\[copied
from workbook.xls]sheet' with 'sheet'? Not sure if this would work, but
might be worth a try.

Ian
 
B

bobclark

On Sat, 09 Oct 2004 21:29:23 GMT, in microsoft.public.excel.misc falling into
the bathtub with your monitor, the short circuit caused the following to
mysteriously appear from your keyboard:

~>~>I am repairing a repetitive worksheet where only half of the range names
~>were
~>recovered. When I copied from another sheet the non-existent range names
~>copied
~>over as links i.e. 'c:\directory\[copied from workbook.xls]sheet'!cellsand
~>i
~>want to rename them (in bulk) to 'sheet'!cells. As half of the range names
~>are
~>correct, how (if I can) do I do this without getting cramps with
~>insert|name|define and correcting every entry individually? or can it be
~>done?
~>
~>Have you tried Excel's Replace facility to replace 'c:\directory\[copied
~>from workbook.xls]sheet' with 'sheet'? Not sure if this would work, but
~>might be worth a try.
~>
~>Ian
~>

That would be way too easy, not microsnots method of complicate complicate
complicate.
 
M

Myrna Larson

Go to Edit/Links, and change the entry for the old workbook: replace it by
saying the source is the current workbook.

If that doesn't eliminate everything, use Search and Replace.
 
D

Dave Peterson

How about:
Edit|links|change source?

Ah, that didn't work for links in Names.

But Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager
did work!!!

You can find it at:
NameManager.Zip from http://www.bmsltd.ie/mvp

I dumped the names to a worksheet (an icon on the name manager dialog).
(the icon has a "create a list of all names..." tooltip)
I modified the "refers to local" in column B
then I used the icon with "Update selected names..." as the tooltip to update
the names in the workbook.

And get a copy of this addin, too:

Bill Manville's FindLink program:
http://www.bmsltd.ie/MVP/Default.htm

Bot those addins will make life simpler for you.

I am repairing a repetitive worksheet where only half of the range names were
recovered. When I copied from another sheet the non-existent range names copied
over as links i.e. 'c:\directory\[copied from workbook.xls]sheet'!cells and i
want to rename them (in bulk) to 'sheet'!cells. As half of the range names are
correct, how (if I can) do I do this without getting cramps with
insert|name|define and correcting every entry individually? or can it be done?

TIA HTH

Bob Clark
Click to expand...
 
Top