I'm struggling to get a formula to populate a list

[Macjay22]

Hi,

I am trying to put together a new staff rota on Excel and I am having a nightmare. I am looking to create a rota which takes names from my available list and then populates my 'duty spreadsheet'. I have 4 members of staff handling telephone enquiries - 2 in the morning and 2 in the afternoon. I also have 2 members of staff on reception duty per day. Each (available) member of staff must do 1 day on reception and a half day of telephony work per week. I'm having a nightmare trying to figure out how to get this spreadsheet to populate automatically. Can it be done? I just cant get to grips with formulas!! Help!

Please find my attempts thus far attached.

[Melvinrobb]

Do you want 30 names in each of the 4 week tables?
no Duplicates?
Could you reupload the file, and using a red font (just an example), show what you want randomly populated, and where, to help clarify?
It appears you have six slots on Monday of week 1, and no duplicates can exist. How is tuesday handled? can it be the same person as monday? does every person have to appear in a minimum, or a maximum number of slots?

[Macjay22]

Hi,

Thanks for your help.

Yes, each week requires 30 names. The reception rota duty requires 10 members of staff - 2 to be on reception duty each day for the full work week.

Similarly, telephone duty is split into half days, with 2 members of staff on duty in the morning and a different 2 in the afternoon. With regards to this, my commitment is to try to arrange this so that nobody is on telephone duty for more than a half day in any given week though, accounting for sickness/annual leave, this may not always be possible. Monday is treated the same as every other day in the week. Also, the telephone duty must not conflict with the reception duty – different names must be present on any given day as one person cannot fulfil both responsibilities simultaneously.

Any help you could provide would be hugely appreciated. I have further explained what i am attempting within the newly attached spreadsheet. Thanks for any and all help.

[Melvinrobb]

What you are describing is pretty complex. I feel I have wrapped my head around the particular scenario, but won't be able to put something together If someone is able to help you out sooner, great.

[Melvinrobb]

It requires 30 names, but only 22 people will fill the table. Instead of the numbers 1 through 30, I used the numbers 1 through 22, with 1 and 2 being in Reception for the entire week. I feel I may not be interpreting your requirements correctly, but you will just have to clarify.
When it comes to dealing with Vacations, that will be difficult. It is unclear how you would even handle that, as you don't have another employee available to take their place.

[Macjay22]

That's fantastic. Thanks for your help.