One easy way is to use iterative calculations. However, that comes

with its own baggage.

To turn on iterative calculations, select Tools | Options... |

Calculation tab. Check on the box for Iteration.

Now, suppose cell E1 controls whether or not a new value is to be

calculated. If it contains FALSE, no change should be made to the

existing value. If it is TRUE, a new function value is desired.

To generate a random number using this technique, in some cell, say C3,

enter the formula =IF(E1,RAND(),C3) Set E1 to TRUE and C3 will contain

a new random number each time Excel recalculates the worksheet. Set E3

to FALSE and the last random number will remain unchanged. Not even a

force full recalculation (CTRL+SHIFT+ALT+F9) will change that value!

To use this technique with today's date, use DATE() instead of RAND()

in the formula in C3.

--

Regards,

Tushar Mehta, MS MVP -- Excel

www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials

Custom MS Office productivity solutions