Random string from an array Excel

Question

I'm just wondering if anyone can solve a simple query in Excel. Basically I want a randomly generated string from an array.

The formula I have is this:

=INDEX({"Day","Night","Mixed"},RANDBETWEEN(1,3))

This works, however, whenever I switch between worksheets the values change and are not fixed once randomly selected.

Anyone have any ideas?

Answer 1

Go to options -> formulas -> enable iterative calculation
Then use a formula like this in B1:

  =IF(A1="","",IF(B1="",INDEX({"Day","Night","Mixed"},RANDBETWEEN(1,3)),B1)

If you empty out A1 then B1 will also be empty. If you put anything in A1 then B1 will choose randomly and stay with it till you empty out A1 again (where B1 will also be empty again)

Alternatively just copy you formula and paste "values only"... but the formula will be gone that way...