Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: Can I change the date formula from mm/dd/yyyy to "mmm-yy" et | Excel Discussion (Misc queries) | |||
Cannot change a formula | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
HOW TO USE A FORMULA TO CHANGE CELL COLOR ACCORDING TO DATE RANGE. | Excel Worksheet Functions | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |