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.



Nitin SontakkePosted Jul 19, 2023, 4:42 AM
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
First LastPosted Jul 19, 2023, 6:45 PM
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