Solved: How to fix a formula value without vba

[khaledocom]

Hi all,

An example:

in c1 there's a formula = a1+b1

a1=10 and b1=20 then value in c1 should be 30.

My question: is it possible to make the first value captured by formula in c1 not to change (fix the value by using a sort of formulas)

later I'll change value in a1 and b1 but I want c1 to remain as it is 30.

Can it be done without using vba or not?

BY the way the original formula was as follows:


=MID(MID(F1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},F1&"0123456789",FIND("[",F1,1))),255),1,6)+1-1

[mikerickson]

No, it cannot be done without VBA.
You could use a UDF like this
Function FreezeValue(Frozen As Boolean, unfrozenValue As Variant) As Variant
Static fv As Boolean
If Frozen Then
If Not fv Then
FreezeValue = unfrozenValue
Else
FreezeValue = Application.Caller.Text
If IsNumeric(FreezeValue) Then FreezeValue = Val(FreezeValue)
If IsDate(FreezeValue) Then FreezeValue = CDate(FreezeValue)
If LCase(FreezeValue) = "true" Or LCase(FreezeValue) = "false" Then FreezeValue = CBool(FreezeValue)
End If
fv = True
Else
FreezeValue = unfrozenValue
fv = False
End If
End Function


When put in a cell, =FreezeValue(C1=1, A1+B1) will show the value of A1+B1 and change as those cell values are changed, whenever the Frozen term is False (when C1<>1 in the example). Setting C1 to 1 will freeze the value returned by the UDF.

[khaledocom]

Thanks a lot brother, this was useful.