+ Reply to Thread
Results 1 to 9 of 9

Randomizer toggle for Excel

  1. #1
    Registered User
    Join Date
    08-21-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    3

    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. #2
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    860

    Re: Randomizer toggle for Excel

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

  3. #3
    Registered User
    Join Date
    08-21-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    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. #4
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    860

    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. #5
    Forum Contributor
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    167

    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)
    Row row row your boat
    Gently down the stream

  6. #6
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    860

    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. #7
    Forum Contributor
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    167

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

  8. #8
    Registered User
    Join Date
    08-21-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    3

    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. #9
    Forum Contributor
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    167

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Toggle Hide/Unhide Cols & Toggle adj column width
    By anare in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-20-2018, 11:04 AM
  2. [SOLVED] how do i make a toggle button change other toggle buttons value to false
    By kevinu in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-10-2018, 04:19 PM
  3. Replies: 1
    Last Post: 02-13-2014, 10:55 PM
  4. Excel Quiz question randomizer
    By macquhele in forum Excel General
    Replies: 4
    Last Post: 01-26-2014, 02:01 PM
  5. Randomizer with out duplicates
    By Treblig in forum Excel General
    Replies: 3
    Last Post: 07-15-2009, 02:58 PM
  6. Randomizer Help
    By MGMRAM in forum Excel General
    Replies: 6
    Last Post: 10-16-2006, 10:42 PM
  7. [SOLVED] I'm looking for info on the Excel Randomizer feature
    By PJR in forum Excel General
    Replies: 1
    Last Post: 03-30-2006, 06:00 AM

Bookmarks

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