How do I get Excel to select a random entry for me?

[Solitaire]

I heard that I shoudl be able to have Excel make a random selection of a
record for me (for a raffle prize). Is this possible? And if so, how do I
do this?

 

[JP]

Here is a UDF you can use, the instructions are below.


Function DrawOne(InRange As Variant)
'
' highlight a range and this function returns one randomly selected
value,
' text or number
' =DRAWONE(A1:C100)
'
'
Randomize
DrawOne = InRange(Int((InRange.count) * Rnd + 1))

End Function


HTH,
JP

 

[Gary''s Student]

First put your records in a list say from A1 thru A1000. In another cell:

=INDIRECT("A" & RANDBETWEEN(1,1000))

 

[hkaplan2]

I heard that I shoudl be able to have Excel make a random selection of a
record for me (for a raffle prize).  Is this possible?  And if so, howdo I
do this?

Or you can use this one: =INDEX($A$1:$A$12,randbetween(1,12))

Where A1:A12 = a list you want to select randomly from.

 

[Solitaire]

Thank you, JP.

I really appreciate your help, but I'm sorry to say that I'm not quite
following. I can usually figure things out intuitively, but I think I need
you to be more literal, if you don't mind (e.g., step one: do this, step two:
do this). If you have the time, I would greatly appreciate it.

Solitaire

 

[HKaplan]

Do this:
#1 - create a sample list of names in Column A. Create 10 names down
the column (i.e. A1=Bob, A2=Sam, etc.)
#2 - In Cell B1 (or any cell you choose, paste this formula: =INDEX($A
$1:$A$10,randbetween(1,10))
#3 - View the result. It should display a random name. Copy the
formula to multiple cells and you will see multiple random names
selected (do this just to show the random feature at work).
#4. Randbetween selects a random number between the numbers you
enter. Randbetween(1,10) selects any number at random between 1 and
10. Index is an Excel method to choose a cell within a range.
Literally the formula means this:
=index(within this range,select this cell).

Get it now?

 

[Hammy]

Thank you, yes. I really appreciate you explaining this to me in more
detail.

 

[JP]

Here you go:

1. Open Excel, click Alt-F11 to go to the VB Editor
2. Click Insert>Module on the toolbar
3. Paste this code into the module:

Function DrawOne(InRange As Variant)
'
' highlight a range and this function returns one randomly selected
value,
' text or number
' =DRAWONE(A1:C100)
'
Randomize
DrawOne = InRange(Int((InRange.count) * Rnd + 1))

End Function

4. Make a note of the module name (i.e. 'Module1', 'Module2' etc)
5. In your worksheet enter =DRAWONE(your range)

For example if you have the names for the raffle in A1 through A10,
enter =DRAWONE(A1:A10) in any cell. If the formula returns a NAME
error, you will have to prefix the formula with the module name from
step 4. For example =Module1.DRAWONE(A1:A10) if you pasted the code
into Module1.

If you need further assistance check out http://www.rondebruin.nl/code.htm
on how to paste code

HTH,
JP

Thank you, JP.  

I really appreciate your help, but I'm sorry to say that I'm not quite
following.  I can usually figure things out intuitively, but I think I need
you to be more literal, if you don't mind (e.g., step one: do this, step two:
do this).  If you have the time, I would greatly appreciate it.

Solitaire