Generate a random result...

[Lukerz]

Hey guys

I don't use excel very often, but I decided I would for a little
project I'm working on. I have a list of words on my excel spreadsheet,
and I want to press a button and have excel give me a random word from
the list. I've seen it done by one of the technicians at my school when
they were sorting something out for assembly (they selected the winner
of a competition by using a random selector thing in excel)

Any help would be amazing appreciated

 

[daddylonglegs]

Here's one way to do that....

Assuming your list of words is in A1:A10 this formula gives you one of
those at random

=INDEX(A1:A10,RAND()*ROWS(A1:A10)+1)

note that every time worksheet is re-calulated, the result is generated
again, you can trigger that by hitting F9

 

[Lukerz]

Thanks man, but where do I type that formula?

 

[davesexcel]

this could work:

in sheet2
A1:A5
john
jason
dave
george
leo
B1:B5
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()


copy this macro to a module
Sub Macro1()
'
Application.ScreenUpdating = False
Sheets("Sheet2").Select
Range("A1:B5").Select

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("Sheet1").Select
Range("C2").Select
End Sub

Go to sheet1
create a button using forms
assign this macro to a button

now in sheet1 A1 enter this :
=Sheet2!A1

click on the button to get random names

 

[davesexcel]

Here's one way to do that....

Assuming your list of words is in A1:A10 this formula gives you one of
those at random

=INDEX(A1:A10,RAND()*ROWS(A1:A10)+1)

note that every time worksheet is re-calulated, the result is generated
again, you can trigger that by hitting F9

Awsome,
Instead of using my formulas use daddylonglegs, you could still asign a
macro to a button to generate the calculation something like this

Sub Macro3()
'Calculates worksheet
Calculate
End Sub

you can enter the formula in Cell B1 for now just to see how it works

 

[Tom Ogilvy]

In the cell where you want the random word to appear. As written, is should
be on the same sheet as the list.

 

[Guest]

another way
two column table
column a 0 to 9
column b a name beside each number
a cell somewhere on the worksheet say c1 =int(rand()*10)
cell c2=vlookup(c1,a1:b10,2 false)
or in c1=VLOOKUP(INT(RAND()*10),H1:I10,2,FALSE)
Rand() returns an evenly distributed random number greater than or equal to
0 and less than 1. A new random number is returned every time the worksheet
is calculated so you will have to devise a multiplier to suit the number of
names you have.