How to use a VLOOKUP to take formula rather than result of target cell

Is there anyway I can use a VLOOKUP to take a formula from another cell rather than the result in the target cell. I don't want the actual formula displayed I want the formula to calculate from the location of the vlookup.

The formula contains various INDIRECT references and is on a different sheet to the VLOOKUP (not sure if this complicates things).

e.g.

On Sheet1 I have a list of data and for each line of data a different formula is likely to apply. I have written the formulas on the Formula sheet and in this sheet they just return zero value or #DIV/0! error, which is fine and expected. If I copy and paste these formulas into Sheet1 they work great. So what I want to do is have a VLOOKUP on Sheet1 that tells the sheet which formula to use.


example of formulas on formula sheet;

=((INDIRECT(ADDRESS(ROW(),Formulas!$H$3,4))*INDIRECT(ADDRESS(ROW(),Formulas!$I$3,4))*INDIRECT(ADDRESS(ROW(),Formulas!$J$3,4)))/1000000000)*INDIRECT(ADDRESS(ROW(),Formulas!$K$3,4))
=INDIRECT(ADDRESS(ROW(),Formulas!$H$6,4))/4.54609
=INDIRECT(ADDRESS(ROW(),Formulas!$H$9,4))/INDIRECT(ADDRESS(ROW(),Formulas!$I$9,4))

Whilst unlikely, I could potentially have over 100 different formulas which is why I have decided to try this method rather than an IF formula, but have only shown 3 formulas in this example as a basic illustration.

Hope this makes sense, I also attach an example which might make it a little clearer.

hi can you give the direct formula in e2 of sheet named Formula .

So that i can make try at least in another way

Punnam

Hi Punnam, thanks for the reply, the direct version of the tonne to ksheets formula would look like this =((H3*I3*J3)/1000000000)*D3 if typed directly into cell F3 on Sheet1.

Is that what you need?

How may fields you have like i mean different type of Formulas as of know with respect your sheet they are 3 Number

Punnam

I have 12 different units of measure (UoM), so in theory this could result in 132 different formulas. In reality I think I am likely to only use about 10-12 different conversion formulas maximum.

chris01252

in case if your using excel 2007 then we can go for nested if condition for each name you have each formula 12 will not be a bid deal

I am trying with name range .in case of any success it will be more easy
O you can go for Macros or User defined fields

Punnam

Hi Punnam

I continued to play around with this and I think it was the INDIRECT references that were really causing the headaches. I managed to get it to work by removing any indirect references and now using the EVALUATE function. I have attached example of the solution.

Thanks for you help with this.

Hi chris01252 ,


Thanks for the feedback & Sharing the solution.
This is really nice one i tried the same with name manager but no luck,
Because of you i have came to know new function evaluate
Punnam