Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to replace a function with its resulting reference in a formula?
Hello
How to replace a function with its resulting reference in a formula? For example, How to replace =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11. I need to replace in 1000 formulas/cells automatically. |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to replace a function with its resulting reference in a formula?
Is this a total discovery requirement - in other words you don't know
anything about the formula in the cells, so you would have to parse the formula and have procedures to handle any excel function which might be there? Please describe your rules for how each function should be handled and all combinations of functions. -- Regards, Tom Ogilvy "Dmitry Kopnichev" wrote in message ... Hello How to replace a function with its resulting reference in a formula? For example, How to replace =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11. I need to replace in 1000 formulas/cells automatically. |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to replace a function with its resulting reference in a formula?
The formulas are sums of HLOOKUP functions, for example
=HLOOKUP(1...)+HLOOKUP(2...). I have to change the formulas to sums of references, from which the HLOOKUP functions returend values, for example =b11+z11. "Tom Ogilvy" сообщил/сообщила в новостях следующее: ... Is this a total discovery requirement - in other words you don't know anything about the formula in the cells, so you would have to parse the formula and have procedures to handle any excel function which might be there? Please describe your rules for how each function should be handled and all combinations of functions. -- Regards, Tom Ogilvy "Dmitry Kopnichev" wrote in message ... Hello How to replace a function with its resulting reference in a formula? For example, How to replace =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11. I need to replace in 1000 formulas/cells automatically. |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to replace a function with its resulting reference in a formula?
The formulas are only sums of HLOOKUP functions, for example,
=HLOOKUP(1...)+HLOOKUP(2...). I have to change the formulas to sums of references, from which the HLOOKUP functions returend values, for example, to =b11+z11. "Tom Ogilvy" сообщил/сообщила в новостях следующее: ... Is this a total discovery requirement - in other words you don't know anything about the formula in the cells, so you would have to parse the formula and have procedures to handle any excel function which might be there? Please describe your rules for how each function should be handled and all combinations of functions. -- Regards, Tom Ogilvy "Dmitry Kopnichev" wrote in message ... Hello How to replace a function with its resulting reference in a formula? For example, How to replace =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11. I need to replace in 1000 formulas/cells automatically. |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to replace a function with its resulting reference in a formula?
Having read the thread, it is obvious that you will need VBA, at least
for part of the problem, that is to break the formula into the individual component HLOOKUP()'s. If all the formulas are sums of 2 HLOOKUP()'s, your job is somewhat easier, because you can write a UDF to isolate the two parts and then populate two helper columns w/ these formulas. Once you have the components separately, you can then resort back to formula programming. When you know the *result* of a lookup function you can find the column (i.e. the position in an array) with INDEX(MATCH(...)) or similar constructs. But given that columns grow alphabetically instead of numerically, you might need to play with R1C1 reference style. Overall it is rather complicated. Write if you need pointers to any of the steps. |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to replace a function with its resulting reference in a formula?
Assuming your data, named t_array, looks like this,
9 11 21 10 13 27 1 14 25 2 13 28 6 21 28 8 18 22 3 15 25 4 19 30 5 12 27 7 14 30 and you want to find =VLOOKUP(8,t_array,2,0)+VLOOKUP(10,t_array,3,0) and convert it to =R6C2+R2C3 (=18+27) (=45) Enter this formula ="^"&"="&ADDRESS(MATCH(8,INDEX(t_array,,1),0),2,1, 0)&"+" &ADDRESS(MATCH(10,INDEX(t_array,,1),0),3,1,0) Select this cell and Copy Paste Special Value Then erase the ^ I leave it to you to convert to HLOOKUP and A1 reference. |
#7
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to replace a function with its resulting reference in a formula?
Thank you, Herbert Seidenberg.
I have more than a thousand formulas as =VLOOKUP(8,t_array,2,0)+VLOOKUP(10,t_array,3,0) on a sheet. They have different col_index_num s when in the same row. They have different lookup_value s and number of LOOKUP functions when in the same column. How to change the formulas automatically? "Herbert Seidenberg" wrote in message oups.com... Assuming your data, named t_array, looks like this, 9 11 21 10 13 27 1 14 25 2 13 28 6 21 28 8 18 22 3 15 25 4 19 30 5 12 27 7 14 30 and you want to find =VLOOKUP(8,t_array,2,0)+VLOOKUP(10,t_array,3,0) and convert it to =R6C2+R2C3 (=18+27) (=45) Enter this formula ="^"&"="&ADDRESS(MATCH(8,INDEX(t_array,,1),0),2,1, 0)&"+" &ADDRESS(MATCH(10,INDEX(t_array,,1),0),3,1,0) Select this cell and Copy Paste Special Value Then erase the ^ I leave it to you to convert to HLOOKUP and A1 reference. |
#8
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to replace a function with its resulting reference in a formula?
All the formulas in the same columns are the same except the row_index_num.
The formulas in the same rows are the same except the number of HLOOKUP functions and lookup_value's. "vezerid" wrote in message ups.com... Having read the thread, it is obvious that you will need VBA, at least for part of the problem, that is to break the formula into the individual component HLOOKUP()'s. If all the formulas are sums of 2 HLOOKUP()'s, your job is somewhat easier, because you can write a UDF to isolate the two parts and then populate two helper columns w/ these formulas. Once you have the components separately, you can then resort back to formula programming. When you know the *result* of a lookup function you can find the column (i.e. the position in an array) with INDEX(MATCH(...)) or similar constructs. But given that columns grow alphabetically instead of numerically, you might need to play with R1C1 reference style. Overall it is rather complicated. Write if you need pointers to any of the steps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the Cell Reference Resulting from MAX function | Excel Worksheet Functions | |||
How to replace a function with its resulting reference in a formula? | Excel Worksheet Functions | |||
How to replace a function with its result or resulting reference in a formula? | Excel Worksheet Functions | |||
How to replace a function with its result or resulting reference in a formula? | Excel Programming | |||
Replace reference inside formula | Excel Programming |