Is there a way to "trick" a formula out of its volatility and hold a value from a previous calculation?
For example
CELL A1 = RAND Value Between 1 and 10 (or whatever)
CELL B1 = Dropdown menu: "Randomize" and "Freeze"
A1 formula would do something like:
IF(B1="Freeze",["Formula to Maintain Currently Displayed Value"],[RAND(etc..))
I've been experiminting with using INDIRECT with the term RAND in order to turn the RAND on and off in a formula with a dropdown box, but this only stops it from calculating, it does not hold its previously randomized value.
I'm guessing pretty soon I'm going to have to bite the bullet and start figuring out VB... But if there's a clever way to do this with formulas I'd love to hear it.
Thanks
Bookmarks