excel return formula with relative cell

Question

I have 2 worksheets in my excel, the first sheet allows me to select a calculation method from a drop down list and input the variable for the calculation (shown in green cell, the column in blue shows some constant number). The result entry will search for the corresponding calculation formula from my second worksheet (database), then paste the formula to the sheet 1, I need the formula to calculate using the cells in sheet 1 instead of cells in my database.

enter image description here

currently I created an user defined function called Eval as below:

Function Eval(ref As String)
  Eval = Application.Evaluate(ref)
End Function

by combining the Eval with vlookup :=Eval(VLOOKUP(A3,Database!A2:E10,5,FALSE)) I will get the result that the calculation equation uses the cells from my database, how can I achieve the result which the formula takes cells in sheet 1 during calculation?

Dirk Reichel Dirk Reichel · Accepted Answer · 2016-08-15T04:46:57

One simple way would be to use all formulas within one CHOOSE like this:

=IFERROR(CHOOSE(SUMPRODUCT(MATCH(A2,"Calculation "&ROW($1:$9),0)),B2*C2*D2,B2*C2-D2,B2+C2-D2,B2^2-C2+D2,B2*D2-C2^2,B2+C2*D2,B2*C2-C2*D2,B2-D2-C2*D2,C2-D2*B2),"")

Another would be to use the Application.Caller like:

Public Function eval(ref As String)
  eval = Application.Caller.Parent.Evaluate(ref)
End Function

This ensures the use of of the parent of the caller (the sheet with the eval() formula) to be used as main-ref.

EDIT
Keep in mind that your "rows" are static in the formulas. Going for "Calculation 4" will use row 5 (and not the row of your formula from the first sheet). For this you could use something like:

=Eval(SUBSTITUTE(VLOOKUP(A3,Database!A2:E10,5,FALSE),"##",ROW()))

While all row-numbers should be changed to ## (or whatever unique identifier you like). Then "Calculation 1" would look like: =B##*C##*D##

If you still have any questions, just ask :)

excel return formula with relative cell

1 Answers