Need help relating a list of random numbers to a list of names so that i can create a rota

Sorry, I'm a bit of a novice in these matters and have been having some trouble. I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). Can anybody give me any advice about how best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously). I would appreciate any advice!

Hi

Wouldn’t it be easier and fairer to just rotate staff through the different tasks? If you use a genuinely random system, you’ll find some people repeating tasks and some seldom doing some. Also, if you have more staff than roles to fill, a random system will not distribute the days off equally in the short term. These things will average out in the long term, but staff may not see it that way if they get several days without work.

Anyway … one way to randomise task allocation and ensure there is no duplication of roles would be to put random numbers next to each name and then allocate tasks according to the rank of those random numbers. So the highest random number is reception 1, the second highest is reception 2, and so on. Each time you refresh the sheet the random numbers and their ranks will change, allocating that position to different people.

But given that there are 30 distinct duties and only 22 staff members, i would have to devise a formula to randomly determine who the 8 additional staff members were. This is something i have been struggling with - i have also found it tremendously difficult to 'translate' that kind of information into anything resembling a staff rota. I don't know how to relate the unique random numbers to the names/tasks to allow the creation of an instantaneous rota. I'm obviously missing something... I have attached what i have done thus far.