How to replace a function with its resulting reference in a formula?

[Dmitry Kopnichev]

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.

 

[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.

 

[Dmitry Kopnichev]

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.

 

[Dmitry Kopnichev]

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.

 

[vezerid]

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.

 

[Herbert Seidenberg]

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.

 

[Dmitry Kopnichev]

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?

 

[Dmitry Kopnichev]

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.