Randomizer toggle for Excel

[DJ_Tanner]

I was trying to figure out a way to get a randomize toggle to work in Excel and am really chasing my own tail here and wondering if anyone had any good solutions.

Basically what I am looking for is a Check Box to check/uncheck to start a random function in a set of other cells. So that the cells will randomize when the check is on but will remain the same when the check is off. I basically started here:

A1: =IF(D1=TRUE,RANDBETWEEN(1,100),)

But this gives me the random value when checked but 0 unchecked. Using "" as the FALSE condition gives a blank cell, and other than creating a circular reference I kinda ran outta ways to go about this. Does anyone know a way to get this interaction to work. I am trying to avoid any VBA or Macro codes as I wanted this in a workbook given out and used by a wide range of skill levels and many would be unable to work/understand those.

Thanks!

[janmorris]

you have not specified what the "else" is, which is why it gives a blank cell

[DJ_Tanner]

Right, I know why I am returning the blank or 0, but I want it to return the previous value. So basically you can turn the randomize function on/off with the click box. i.e. Turn random on A1 = 7, turn it off it remains 70.

[janmorris]

if the cell got its original value from a formula, then you need that formula as the "else" inside the formula you put above.

[Metoo7]

you have to do some setting as following steps:
Options - Formulas - Calculation Options - tick "Enable iterative calculation"

and change your formula a little bit:
A1: =IF(D1=TRUE,RANDBETWEEN(1,100),A1)

[janmorris]

Please Login or Register  to view this content.

how does this return the value back to what it was before it was randomized?

[Metoo7]

referring to your floor #3, you mentioned that:

Right, I know why I am returning the blank or 0, but I want it to return the previous value.

But you also said that : Turn random on A1 = 7, turn it off it remains 70.

You may want to change the formula to

Please Login or Register  to view this content.

[DJ_Tanner]

Sorry the 70 was a typo.

The turning on iterative calcs does the trick, so thanks. It does create a circular reference and a step I am a little hesitant to add but I think it may be the lesser of two evils here.

Thanks again for everyone pitchin in!

[Metoo7]

Yep, otherwise you may have to use VBA. But an Excel file with Macro script may not be suitable for your colleagues. There is no perfect solution.