Roster from Pivot or formula?

Hi,

My boss asked me about our roster and doing it smarter.

He wants to know if we can have drop-downs in each of the roles to select from the staff for the day.
There's actually about 30 roles and people get swapped around each day. (And about 50 staff to choose from.)
I immediately thought of using a pivot.

The he asked, "How can you make it so that it excludes people already selected, so they don't mistakenly get assigned to 2 or more roles?"

e.g. John/counter1, Betty/counter2, Pete/greeter, Sue/appointments

I thought, a pivot with lots of if/then formulas, but maybe you have a better idea?

Your input would be most appreciated!!
Thank you.

Can do.
Here is a mockup file.

I just worked out a simple solution.
Select from a drop-down using Data Validation,
then conditional format for duplicate values.

Not so elegant, but if you have a more elegant solution, I'd love to hear.

Test it.
for list

Formula:

=IFERROR(INDEX($B$4:$B$53;SMALL(IF((Sheet1!$B$4:$B$53<>"")*(COUNTIF(Sheet2!$B$5:$AE$35;Sheet1!$B$4:$B$53)=0);ROW($B$4:$B$53)-ROW($B$3));ROW(A1)));"")

And for data validation

Formula:

=OFFSET(Sheet1!$C$3;1;;ROWS(Sheet1!$C$4:$C$53)-COUNTIF(Sheet1!$C$4:$C$53;""))

Thanks BMV, I'll play with those and let you know how I get on. :D