# Randomizer toggle for Excel

1. ## Randomizer toggle for Excel

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!

2. ## Re: Randomizer toggle for Excel

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

3. ## Re: Randomizer toggle for Excel

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.

4. ## Re: Randomizer toggle for Excel

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

5. ## Re: Randomizer toggle for Excel

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)

6. ## Re: Randomizer toggle for Excel

``Please Login or Register  to view this content.``
how does this return the value back to what it was before it was randomized?

7. ## Re: Randomizer toggle for Excel

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
Formula:
`Please Login or Register  to view this content.`

8. ## Re: Randomizer toggle for Excel

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!

9. ## Re: Randomizer toggle for Excel

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1