Formula in CF

[dlamarche]

Hello I know that I can do a formula like:

$B2>$K$1 in the formula box to apply a DF all cells in column B where the
value is higher than the value in K1. Good. Now what I was asked to do is to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional operator
too which, say, is in K2. So K1 contains 3500 and K2 contains > and I want to
do a formula in the CF formula box. The user can change the value in K1 and
type > or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=4500>6000

I have played with indirect a bit to create a reference in a concatenation.
It seems that CF can only accept a real formula and not a one built as the
result of an expression.

Any idea? Thank you.

 

[Charabeuh]

Try this:

in a module copy this VBA function:
---------------------------------------
Public Function Compute(x, y, z)
Compute = Application.Evaluate("=" & x & y & z)
End Function
---------------------------------------

In the conditionnal format dialog box, enter the formula:
=Compute(B1,$K$1,$K$2)

with B1 is the cell to format
$K$1 is for example >
$K$2 is for example 3500

 

[Charabeuh]

Prefer this function :
-----------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute_n = Application.Evaluate(xS)
End Function

 

[Charabeuh]

too fast !!!! prefer this function:
------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute = Application.Evaluate(xS) '<== here the error was...
End Function

 

[T. Valko]

Try this:

=AND(COUNT(K$1,B2)=2,IF(K$2=">",B2>K$1,IF(K$2="<",B2<K$1)))

 

[Dana DeLouis]

xS = "=" & " " & x ...etc

As a side note, "=" is usually not required to "Evaluate"

Public Function Compute(ParamArray v())
Compute = Evaluate(Join(v))
End Function


=Compute(A1,B1,C1)

= = = =
Dana DeLouis

too fast !!!! prefer this function:
------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute = Application.Evaluate(xS) '<== here the error was...
End Function

 

[Charabeuh]

Hello,

As a side note, "=" is usually not required to "Evaluate"

Thanks! I will keep it in mind.

==> Why I prefer : x.Address & " " & y.Value & " " & z.Address
instead of : x & y & z

I have a french excel 2007 and use the "," for decimal numbers
if x=3,5 and y =< and z = 5 then x & y & z gives : 3,5<5
When VBA evaluate this, the result is an erreur of value.
VBA only understand american forms of decimals.
When the formula uses addresses the result is right.
the formula becomes : A1 < C1 that is correctly evaluated.
This formula should work with either the '.' or ',' for decimal numbers.

I could also change my regional options to use the '.' instead
of ",' for decimals to use Evaluate(Join(v))

xS = "=" & " " & x ...etc

As a side note, "=" is usually not required to "Evaluate"

Public Function Compute(ParamArray v())
Compute = Evaluate(Join(v))
End Function


=Compute(A1,B1,C1)

= = = =
Dana DeLouis

 

[Dana DeLouis]

I have a french excel 2007 and use the "," for decimal numbers

When the formula uses addresses the result is right.

Thanks. I learned something new. :>)

Dana