how to conditionally 'freeze' the value in a cell?

[excelquest23]

Hello,

I would like for the value in cell A1 to be randomly drawn between 5 and 10 if the value of A2 is not 1, and to be 'frozen' with the existing value in A1 if A2 is equal to 1.

So, if A2 has a value 2 then value in A1 could be 8.

If value of A2 is then changed to 1, I would like for value in A1 to remain 8.

Is there an easy way to do this with an IF command? I tried the following but it didn't work:
=IF(A2=1,A1,(RANDBETWEEN(5,10)))

Thanks!

 

[robert phillips]

Use the worksheet change event to track changes in A2 and update A1 with the vba rnd function.

When A2<>1, Rnd number generated & put into A1

Then When A2=1, You Can Do Nothing and A1 will not be changed
OR
To guarantee that A1 retains the same value as the last time it was updated when A2<>1
You can simply save the value of A1 each time it is changed and push it back to A1
when A2=1
OR
You can regenerate the same number by supplying the Rnd function with 0



Private Sub Worksheet_Change(ByVal Target As Range)
Static OldA1 'hold value in case you want to push oldval back to A1

On Error GoTo BeSafe

Application.EnableEvents = False 'momentarily prevent subsequent change event

If Target.Address = "$A$2" Then
If Target.Value <> 1 Then
OldA1 = Int(Rnd() * 5 + 0.5) + 5
Range("A1").value=OldA1
Else
'Do Nothing
'Range("A1").Value = Int(Rnd(0) * 5 + 0.5) + 5 'this regenerates old number to A1
'Range("A1").value=OldA1 'this pushes old value to A1
End If
End If

BeSafe:
Application.EnableEvents = True
End Sub


If you try to do this with excel IF functions, no way to return the cell value to itself because causes circular reference. You would have to have a secondary cell somewhere holding the value you want to put in A1.