Select random cell
 

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

Select random cell
 

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

"SammyJJones" wrote in message
...

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


--
SammyJJones

Select random cell
 

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
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

"SammyJJones" wrote in message
...

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


--
SammyJJones

Cheers Guys,

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

Select random cell
 

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"SammyJJones" wrote in message
...

Cheers Guys,

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


--
SammyJJones