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?

One way:

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


--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
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?

Put this in A15:

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

Hope this helps.

Pete

On Feb 20, 6:19*pm, Sean wrote:
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?

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

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)


--
Biff
Microsoft Excel MVP


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