Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying cell references
I have a excel workbook, in which certain cells are referenced with the User
defined function names. say cell B2 has a reference to user defined function ='C:\ExcelAddin\XLStart\ABC.xla'!getValue(B1,"OWNE R") and B3 has a reference to user defined function ='D:\ExcelAddin\XLStart\XYZ.xla'!getValue(B1,"SALE ") I want to replace all these cell references in the complete sheet containing getValue(....) with only the function name. Like reference to B2 and B3 should be like B2 = getValue(B1,"OWNER") B3 = getValue(B1,"SALE") Please help. Thanks, Sandeep |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying cell references
The Search And Replace feature supports wildcards. So press Ctrl-H, type
"*!GetValue(" (without the surrrounding quotes) in the "Find what" field, "=GetValue(" (without the surrrounding quotes) in the "Replace with" field and press "replace all" button. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying cell references
Thanks for the reply.
I want that the replaced reference for the cell should be like 1. what ever is before the function name i.e. GetValue should be removed. - As you have added the comments, the things are working fine 2. what ever is there after the function name i.e. GetValue should be appended as it is. Now for each cell, the string after the function name is different. say for B2 reference is ='C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!ge tValue(B1,"OWNER","LINE_ITEMS",B12) and for B3 the reference is ='C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!ge tValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS",B12 ) I want that within single find/replace, references should be replaced as for B2 = getValue(B1,"OWNER","LINE_ITEMS",B12) for B3 = getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS",B 12) Please help. Thanks, Sandeep "Stephane Quenson" wrote: The Search And Replace feature supports wildcards. So press Ctrl-H, type "*!GetValue(" (without the surrrounding quotes) in the "Find what" field, "=GetValue(" (without the surrrounding quotes) in the "Replace with" field and press "replace all" button. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying cell references
Not sure I get your point. The Search And Replace I gave you removes
everything before getValue(, and keeps everything as it is after getValue(. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying cell references
Say ,
B2 = 'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!get Value(B1,"OWNER","LINE_ITEMS",B12) B3 = 'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!get Value(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS",B12) You see, the parameters passed to the function are different for both these cells Now to replace the things, First, I need to do the CTRL+H where - FIND value - "*getValue(B1,"OWNER","LINE_ITEMS",B12)" REPLACE with value - "=getValue(B1,"OWNER","LINE_ITEMS",B12)" This will update the B2 reference as required. Second, Again i need to do the CTRL+H where - FIND value - "*getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS" ,B12)" REPLACE with value - "=getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS" ,B12)" This will update the B3 reference as required. So you see, we need to make do the same thing twice, two update the references as the parameters to the same function are different. I want is that using single replace, i can perform update both B2 and B3 at the same time. Something like this: - FIND value - "*getValue(XXX)" REPLACE with value - "=getValue(XXX)" what ever value (here XXX) comes after the function name(here getValue) should be appended to the replace value as it is. Please help. Thanks, Sandeep "Stephane Quenson" wrote: Not sure I get your point. The Search And Replace I gave you removes everything before getValue(, and keeps everything as it is after getValue(. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying cell references
So, in short, instead of performing 2 steps we should be able to update the
references within one steps itself. I need to do this programmtically in VBA Thanks, Sandeep "Sandeep" wrote: Say , B2 = 'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!get Value(B1,"OWNER","LINE_ITEMS",B12) B3 = 'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!get Value(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS",B12) You see, the parameters passed to the function are different for both these cells Now to replace the things, First, I need to do the CTRL+H where - FIND value - "*getValue(B1,"OWNER","LINE_ITEMS",B12)" REPLACE with value - "=getValue(B1,"OWNER","LINE_ITEMS",B12)" This will update the B2 reference as required. Second, Again i need to do the CTRL+H where - FIND value - "*getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS" ,B12)" REPLACE with value - "=getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS" ,B12)" This will update the B3 reference as required. So you see, we need to make do the same thing twice, two update the references as the parameters to the same function are different. I want is that using single replace, i can perform update both B2 and B3 at the same time. Something like this: - FIND value - "*getValue(XXX)" REPLACE with value - "=getValue(XXX)" what ever value (here XXX) comes after the function name(here getValue) should be appended to the replace value as it is. Please help. Thanks, Sandeep "Stephane Quenson" wrote: Not sure I get your point. The Search And Replace I gave you removes everything before getValue(, and keeps everything as it is after getValue(. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying cell references
I never said that you should put in the search and replace boxes what is
*after* the parenthesis of getValue( Try simply with: Find What: *!getValue( Replace with: =getValue( If you want to do it with a program, simply record a macro when you do the Search And Replace operation, then look at the code and eventually adapt the range and run it whenever you want. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying cell references
Try recording a macro when you do it manually. If it works when you do it
manually, the code should work when it runs. Sandeep wrote: So, in short, instead of performing 2 steps we should be able to update the references within one steps itself. I need to do this programmtically in VBA Thanks, Sandeep "Sandeep" wrote: Say , B2 = 'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!get Value(B1,"OWNER","LINE_ITEMS",B12) B3 = 'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!get Value(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS",B12) You see, the parameters passed to the function are different for both these cells Now to replace the things, First, I need to do the CTRL+H where - FIND value - "*getValue(B1,"OWNER","LINE_ITEMS",B12)" REPLACE with value - "=getValue(B1,"OWNER","LINE_ITEMS",B12)" This will update the B2 reference as required. Second, Again i need to do the CTRL+H where - FIND value - "*getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS" ,B12)" REPLACE with value - "=getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS" ,B12)" This will update the B3 reference as required. So you see, we need to make do the same thing twice, two update the references as the parameters to the same function are different. I want is that using single replace, i can perform update both B2 and B3 at the same time. Something like this: - FIND value - "*getValue(XXX)" REPLACE with value - "=getValue(XXX)" what ever value (here XXX) comes after the function name(here getValue) should be appended to the replace value as it is. Please help. Thanks, Sandeep "Stephane Quenson" wrote: Not sure I get your point. The Search And Replace I gave you removes everything before getValue(, and keeps everything as it is after getValue(. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying information from one cell to another without modifying it. | New Users to Excel | |||
modifying this formula to return an empty cell | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Modifying Cell Addresses and operating on them | Excel Worksheet Functions | |||
Modifying cell entries? | New Users to Excel |