Random cell selct/highlighting

[kiboodez]

Hi all,

I was wondering if someone could help me with, probably a very simple, problem.
I have classroom seating plans set out with names in cells to represent desks, and am looking for some way to automatically highlight one of the names at random (completely randomises who I direct questioning to). I can do this easily as a list, but want it to actually highlight the person in the seating position, not just the name. I have attached a file showing how I’ve done it as a list (sheet 1) and an example of my seating plan on (sheet 2).

[martindwilson]

see sheet 2 press f9 to re select

[kiboodez]

Awesome, thanks very much for your help.

Could you tell me how it is done?

[martindwilson]

yep
the cells a1:j9 have the following condtional format
in the formula is option
=A1=INDEX($L$1:$L$26,MATCH(MAX($M$1:$M$26),$M$1:$M$26,0))
this looks at the table on the right l1:m26
in column m1:m26 there is =rand() which generates a random numbers
the formula looks for the maximum rand value
thats this bit
MAX($M$1:$M$26),
then uses match to find out what row its in
MATCH(MAX($M$1:$M$26),$M$1:$M$26,0)
the row is then used in the index f1:f26
to get the value from that cell
say its A
then in conditional formatting you test for true/false
so essentially it says if this cells value is = to result of the index/match return true and use the format selected

[kiboodez]

That's graet, thanks.