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!

 

[Tom Urtis]

Explain how the value in A2 changes:

By manual entry?
By formula?
By a userform command?
Some other way?

Also explain what "between 5 and 10" means to you:

6, 7, 8, 9 (literal meaning)
or
5, 6, 7, 8, 9, 10 (maybe you meant this)
or something else, especially if you want decimals, example 6.9

 

[excelquest23]

Thanks for your response. The value in A2 changes randomly between 0 and 1.

Thanks.

 

[Tom Urtis]

Please read my question for the first time or again, I asked HOW the value changes.

Also see my second edited question.

 

[excelquest23]

A2 changes by manual entry.

I meant the literal meaning (6,7,8,9) for "between 5 and 10"

Thanks

 

[Tom Urtis]

For the worksheet on which this is happening, right click on that sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address <> "$A$2" Or .Cells.count > 1 Then Exit Sub
If .Value = 1 Then Exit Sub
.Offset(-1).Value = Int(Rnd() * 4 + 6)
End With
End Sub

Keep in mind...
If you enter a zero (0) or any number other than 1, or you hit the Delete key, and you see no change in cell A1, it will not mean the code is not working, it will mean that the random number selected happens to be the same number that was in there before, which is going to happen roughly 25% of the time because you only have a possible pool of 4 numbers that can be randomly displayed.

The above code can be modified for stuff like that, but because you did not specify for it, I did not include it.

 

[excelquest23]

Great. Thank you very much!