How to create static results from Random list

AuthorJeremyCollier

New Member
Joined
Nov 11, 2016
Messages
4
Hello all!

I'm at a loss on how to accomplish a static random return that will not change unless the user wants it to. Basically, I am trying to give a random result based on a selection in a list and need the random to not change again unless the user reselects Random. I'll be more specific below.

Dropdown list in A1 contains Yes, No, Random. If Random is selected, a random result picked from elsewhere in the workbook is displayed in B1. I've gotten this part to work fine with a combination of functions based around RANDBETWEEN, but the problem is the results changes every time anything else is changed in the workbook, as per the way RANDBETWEEN works, but I only want it changed if the user clicks on the dropdown list again and selects random again.

I've seen other similar (but not similar enough) questions around this and other forums, but I wasn't able to adapt it to my own need.

Sorry if I'm not very clear or left out important information. While I'm pretty good with excel when it comes to the functions, I have very little experience with manipulating things in ways that were not originally intended...
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
The formula I'm currently using to output a random string from a group is the following:

=IF(J23="Random",INDIRECT(CONCATENATE("K",RANDBETWEEN(23,24)))," ")

This outputs one of the two values found in K23-K24 randomly. However, when anything in the workbook is changed, it causes the value to randomize again.

I thought of a way to make it work, but don't know if it's possible or too complicated.

When "Random" is selected from the dropdown list, the formula is triggered and the random value is created. Then the value that had been created is copied to another cell and the list is reset. Is this possible? To copy just the value of a cells content automatically? Is it possible to run a macro from a formula or program it to run when the dropdown list "Random" is selected?

Thanks for any help.
 
Upvote 0
Update:

Alright, so after searching more online, I have found a workaround that could work for me, but it is acting weird.

I made a Macro (MACRO1) that copies the random value, pastes JUST the value in another cell, then resets the dropdown list. Ran it manually, worked perfectly.

I then found a VBA code and adapted it for my use to call the macro when a Random is entered in the cell, as follows:

Private Sub Worksheet_Calculate()


If Range("J23") = "Random" Then Call Random1


End Sub

The problem I'm coming up with is that when the Macro is called, it runs multiple times. My guess is this is because it is running every time the macro changes anything. While this is okay, since it is essentially spitting out a random result anyway, I wonder if there is any problems that might arise from this.
 
Upvote 0
Code:
Private Sub Worksheet_Calculate()
  Application.EnableEvents = False
  If Range("J23") = "Random" Then Call Random1
  Application.EnableEvents = True
End Sub
 
Upvote 0
It keeps the Calculate event from being retriggered when the procedure makes a change to the worksheet.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top