Hi there,
based on a serie of input values in cells in range $E$4:$E$8 and in a serie of auxiliar booleans ($F$4:$F$8)that are funtions of the input cells, i generate a string expression using the following formula:
="SUMPRODUCT("&IF($F$4=TRUE,"(Data!$C$3:$C$20000='Retention Analysis'!$E$4)*","")&IF($F$5=TRUE,"('Retention Analysis'!$E$5=Data!$D$3:$D$20000)*","")&IF($F$6=TRUE,"('Retention Analysis'!$E$6=Data!$G$3:$G$20000)*","")&IF($F$7=TRUE,"('Retention Analysis'!$E$7=Data!$E$3:$E$20000)*","")&IF($F$8=TRUE,"('Retention Analysis'!$E$8=Data!$F$3:$F$20000)*","")&"($D51=Data!$A$3:$A$20000)*(E$50=Data!$B$3:$B$20000)*"&"(Data!$H$3:$H$20000))"
This formula basically builds dinamically the equivalent string expression of the formula i would need to evaluate based on the input values.
The problem is that i'm not finding a way of evaluating this string expression.
I have already tried to use this vba function:
Function Eval(r As Range) As Variant
Eval = Evaluate(r.Value)
End Function
With no success.
And the indirect function doesn't seems to be a good a approach as i need to evaluate the whole expression together and not just ranges.
Anyone none if this would be possible?
Thanks in advance
based on a serie of input values in cells in range $E$4:$E$8 and in a serie of auxiliar booleans ($F$4:$F$8)that are funtions of the input cells, i generate a string expression using the following formula:
="SUMPRODUCT("&IF($F$4=TRUE,"(Data!$C$3:$C$20000='Retention Analysis'!$E$4)*","")&IF($F$5=TRUE,"('Retention Analysis'!$E$5=Data!$D$3:$D$20000)*","")&IF($F$6=TRUE,"('Retention Analysis'!$E$6=Data!$G$3:$G$20000)*","")&IF($F$7=TRUE,"('Retention Analysis'!$E$7=Data!$E$3:$E$20000)*","")&IF($F$8=TRUE,"('Retention Analysis'!$E$8=Data!$F$3:$F$20000)*","")&"($D51=Data!$A$3:$A$20000)*(E$50=Data!$B$3:$B$20000)*"&"(Data!$H$3:$H$20000))"
This formula basically builds dinamically the equivalent string expression of the formula i would need to evaluate based on the input values.
The problem is that i'm not finding a way of evaluating this string expression.
I have already tried to use this vba function:
Function Eval(r As Range) As Variant
Eval = Evaluate(r.Value)
End Function
With no success.
And the indirect function doesn't seems to be a good a approach as i need to evaluate the whole expression together and not just ranges.
Anyone none if this would be possible?
Thanks in advance