Random cell?

[MarkOW]

Hi, Hope this makes sense to someone.

A1 = Question 1
A2 = Possible answer A
A3 = Possible answer B
A4 = Possible answer C
A5 = Possible answer D

In cells A6, A7, A8 & A9 I would like to display the contents of either A2, A3, A4 or A5 this must be in a random order as I wish to drag the formula down the sheet multiple times for around 20 questions.
Is this possible with a formula or do I need to look at vba ?

Many thanks

Marko

 

[Special-K99]

in B6:B9
=RAND()

in A6:A9
=INDEX(A$1:A$5,MATCH(SMALL(B$6:B$9,ROW()-5),B$6:B$9,0))

If you press F9 to recalculate A6:A9 B6:B9 values will change and produce a different random selection (all without duplicating)

You wont be able to drag it down 20 times, the formula will need readjusting but this is one solution to produce a random selection without producing duplicates.
I'll have a think about dragging it.

You need to define what your final output looks like, cos I can envisage several ways of doing what you've described.

 

[MarkOW]

Hi and thanks for your help,

I looked through the forum and found this formula that does the trick.

=INDEX($C40:$F40,RAND()*4+1)

Many Thanks

 

[Special-K99]

I think that will produce duplicate output if a similar random number recurs, though you havent specified anything about duplicates.