Random Text selection Q

[Sean]

I have 10 text statements in A1:A10. Is it possible to return in A15 1
radom selection from this range, only proviso is that if Z1=0, <blank>
i.e. don't show anything in A15

Is this possible?

 

[T. Valko]

One way:

=IF(Z1="","",INDEX(A1:A10,ROUND(RAND()*9,0)+1)

 

[Pete_UK]

Put this in A15:

=IF(Z1=0,"",INDIRECT("A"&INT(10*RAND())+1))

Hope this helps.

Pete

 

[Sean]

Thanks Pete and T, will test this tomorrow. I guess it will change
based on the sheet calc?

 

[T. Valko]

I guess it will change based on the sheet calc?

Yes, *every* time a calculation takes place it will change.

If you want it to remain static you could set calculation to manual, or,

Enter this formula in B1 and copy down to B10:

=RAND()

Then, select the range A1:B10 and sort on column B any order, doesn't
matter.

Then you could use this formula:

=IF(Z1="","",A1)