Picking random cell from range

[Chris424]

Hello all,

I would like a function or formula that will select a random cell from a range of cells

A1:A5

But it cannot be repeated.

I have found lots when searching but not for both a cell range and non-repeatable.

Is it possible?

Any help would be fantastic!

[Chris424]

I have attached what I am trying to do as I dont think I explained it very well!

I am creating a shift rota.

staff a
staff b
staff c
staff d
staff e

and would like it so that each staff team (a-e) does one of the types of shift per week.
It would need to be covered from 8-17:00 and includes float.

[VBA Noob]

Maybe you could use the technique decribed in this link which uses data validation

http://www.contextures.com/xlDataVal03.html

VBA Noob