CODEWITHSUNDEEP
excel
Khaled Mousa
Khaled Mousa

Reputation: 35

Fetch the appropriate formula from another sheet based on reference

Instead of writing the formula multiple times at multiple instances, I'd like excel to detect the formula based on a defined reference.

enter image description here

Column F should be calculated based on the Operation defined in column E. In this example, I can use IF() as I only have 4 Operations but this question is linked to another scenario where I have over 20 Operations.

enter image description here

I have all the formula's ready but I'd love to understand if there is way to use the appropriate formula based on "Operation" Column.

Upvotes: 0

Views: 76

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

I would approach your situation by labeling your Column A, B, C, or X, Y, Z or something similar for consistent readability with your formulas.

Second thing I would do is build your formula table similar to what you have except that I would change your formula from being sheet and cell references to being you variable references:

A+B+C+D
A-B-C-D
A/B/C/D
A*B*C*D

Make sure the formulas are written with operators the same way you would if you were using a cell reference.

Then based on your lay out on sheet 3 I would use a VLOOKUP:

=VLOOKUP(E2,SHEET4!$A$2:$B$5,2,0)

Place that in F2 and copy down. That will tell you if you are pulling the right formula or not.

In G2 use the following:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,"A",A2),"B",B2),"C",C2),"D",D2)

Check this to see that your substitution of your values for your variables is correct.

Then select cell H2 to make it the active cell. Then go Formula Ribbon and select Define Name from the Definied Names section.

Give a name for your formula such as ANSWER.

For your formula use 

=evaluate(G2)

once you have added the defined formula to the list of formulas go to cell H2 and enter the following:

=ANSWER

now if you want to avoid all those helper columns, you can substitute one formula into the other.

Upvotes: 1

Related Questions