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))

 

[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

 

[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