Select random cell

[SammyJJones]

Simple to explain... probably not as easy to do.... please help!

I have 30 peoples names listed from A1 to A30.... I want excel to
randomly select one of these names and display it in cell C1.

I know there are long ways to do it, but I'd like it to generate a new
random name every time I hit F9 (refresh) or click on something else.

Thank you in advance
Sammy

 

[Biff]

Hi!

One way but repeats are possible:

Enter this formula in C1:

=INDEX(A1:A30,MATCH(RANDBETWEEN(1,30),INDEX(ROW(1:30),,1),0))

This uses the RANDBETWEEN function which requires the Analysis ToolPak be
installed.

Biff

 

[Ragdyer]

Another way, but *doesn't* need the ATP installed ... BUT ... same
stipulation, duplicates are possible, meaning the same name may come up
twice *or more* in a row.:

=INDEX(A1:A30,INT(RAND()*30)+1)

Now, if you don't want duplicates, you could use a formula to create a
random listing of Column A's names, where you could simply go down this
random list and use the names one at a time.
Every hit of <F9> changes this random listing column.

Enter the Rand function in an out-of-the-way section of your WB, say Z1 to
Z30.

Now, enter this formula in any column to create your random listing of
Column A, and copy down for 30 rows:

=INDEX($A$1:$A$30,RANK(Z1,$Z$1:$Z$30))

 

[SammyJJones]

Cheers Guys,

I went with Ragdyer's solution in the end, worked a treat!!!

 

[RagDyer]

Thanks for the feed-back.