mass change table_array Name in VLOOKUP formula
 

Hi,

I have a worksheet of 2003 data using many VLOOKUPs to several Name ranges
in other worksheets, eg, (=VLOOKUP(B4,Bank_2003,2,FALSE)). I want to clone
this worksheet to prepare 2004 data. I have now defined the corresponding
2004 name ranges, eg, Bank_2004, Visa_2004, etc. I have now created a copy
of the 2003 worksheet and call it say 2004 worksheet. Everything is in the
same .xls file.

Is there a quick way of changing all references in the formulas in the new
worksheet from Bank_2003 to Bank_2004, Visa_2003 to Visa_2004, etc; without
editing each cell. I don't want to change the cell ranges for the 2003 Names
because I still need them.

Bank_2003, Bank_2004 are in the same worksheet; Visa_2003, Visa_2004 are in
another worksheet, etc.

Using the Help, the closest I can find is to use Insert Name Apply, but
I can't make it work.

Any help would be appreciated.

TIA,
Gabby

Use Find and Replace, Ctrl-H.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GabbyU" wrote in message
...
Hi,

I have a worksheet of 2003 data using many VLOOKUPs to several Name ranges
in other worksheets, eg, (=VLOOKUP(B4,Bank_2003,2,FALSE)). I want to clone
this worksheet to prepare 2004 data. I have now defined the corresponding
2004 name ranges, eg, Bank_2004, Visa_2004, etc. I have now created a copy
of the 2003 worksheet and call it say 2004 worksheet. Everything is in the
same .xls file.

Is there a quick way of changing all references in the formulas in the new
worksheet from Bank_2003 to Bank_2004, Visa_2003 to Visa_2004, etc;
without
editing each cell. I don't want to change the cell ranges for the 2003
Names
because I still need them.

Bank_2003, Bank_2004 are in the same worksheet; Visa_2003, Visa_2004 are
in
another worksheet, etc.

Using the Help, the closest I can find is to use Insert Name Apply,
but
I can't make it work.

Any help would be appreciated.

TIA,
Gabby

Hi Bob,

Looks like this will do the trick for me. Didn't realised it would be as
simple as that. Many thanks for the help.

Gabby


"Bob Phillips" wrote in message
...
Use Find and Replace, Ctrl-H.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GabbyU" wrote in message
...
Hi,

I have a worksheet of 2003 data using many VLOOKUPs to several Name
ranges
in other worksheets, eg, (=VLOOKUP(B4,Bank_2003,2,FALSE)). I want to
clone
this worksheet to prepare 2004 data. I have now defined the
corresponding
2004 name ranges, eg, Bank_2004, Visa_2004, etc. I have now created a
copy
of the 2003 worksheet and call it say 2004 worksheet. Everything is in
the
same .xls file.

Is there a quick way of changing all references in the formulas in the
new
worksheet from Bank_2003 to Bank_2004, Visa_2003 to Visa_2004, etc;
without
editing each cell. I don't want to change the cell ranges for the 2003
Names
because I still need them.

Bank_2003, Bank_2004 are in the same worksheet; Visa_2003, Visa_2004 are
in
another worksheet, etc.

Using the Help, the closest I can find is to use Insert Name Apply,
but
I can't make it work.

Any help would be appreciated.

TIA,
Gabby

Hi Bob,

Looks like this will do the trick for me. Didn't realised it would be as
simple as that. Many thanks for the help.

Gabby


"Bob Phillips" wrote in message
...
Use Find and Replace, Ctrl-H.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GabbyU" wrote in message
...
Hi,

I have a worksheet of 2003 data using many VLOOKUPs to several Name
ranges
in other worksheets, eg, (=VLOOKUP(B4,Bank_2003,2,FALSE)). I want to
clone
this worksheet to prepare 2004 data. I have now defined the
corresponding
2004 name ranges, eg, Bank_2004, Visa_2004, etc. I have now created a
copy
of the 2003 worksheet and call it say 2004 worksheet. Everything is in
the
same .xls file.

Is there a quick way of changing all references in the formulas in the
new
worksheet from Bank_2003 to Bank_2004, Visa_2003 to Visa_2004, etc;
without
editing each cell. I don't want to change the cell ranges for the 2003
Names
because I still need them.

Bank_2003, Bank_2004 are in the same worksheet; Visa_2003, Visa_2004 are
in
another worksheet, etc.

Using the Help, the closest I can find is to use Insert Name Apply,
but
I can't make it work.

Any help would be appreciated.

TIA,
Gabby