How to build a formula on values and operators defined in cells

Question

I have a formula in cell (in the green box below) referencing a cell in the current sheet (C42) [not shown] and other cells [B134 and D134] in another sheet.

The cells formula:

=IF(AND(C42>'Common For All'!B134,(C42<'Common For All'!D134)),'Common For All'!H134,0)

I just want to build the same formula above using the references to the comparison operators in cols A and C (in the other sheet) instead of hardcoding the comparison operators as they may change. And I would then just change the tables comparison operators and hence the cell will perform dynamically.

The other sheet:

Just not sure how to string them into the formula.

I tried:

=IF(AND(C42&" "&TEXTJOIN(" ",TRUE,'Common For All'!A134:'Common For All'!B134),(C42&" "&TEXTJOIN(" ",TRUE,'Common For All'!C134:'Common For All'!D134))),'Common For All'!H134,0)

It looks like it resolves it, but then fails.

Answer 1

It doesn't work like this. The values that you have concatened are treated as String and NOT a formula.

For that you will need something like Eval function available in VBA and JavaScript.

Read the following link and it might be of help to you.

https://superuser.com/questions/253353/excel-function-that-evaluates-a-string-as-if-it-were-a-formula

Answer 2

As a solution, I created a function to use that evaluates the data in my table based upon what I send it. It returns TRUE or FALSE. 

=IF(AND(EvaluateYearlyPenaltyExpression(C42,'Common For All'!A132,'Common For All'!B132),GetYearlyPenaltyExpression(C42,'Common For All'!C132,'Common For All'!D132)),'Common For All'!H132,0)

Function EvaluateYearlyPenaltyExpression(income As Currency, comparisonoperator As String, threshold As Currency) As Variant
  Dim compareop

  compareop = UCase(Trim(comparisonoperator))

  idx = InStr("AND,OR,XOR", compareop)

  If idx > 0 Then
    'Build expression using Excel logical function (AND(), OR(), XOR()).
    expr = compareop & "(" & income & "," & threshold & ")"
  Else
    'Build and expression like "x + y", "x < y", ...
    expr = income & compareop & threshold
  End If

  EvaluateYearlyPenaltyExpression = Evaluate(expr)
End Function