Dynamic dropdown list|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hello!

Attached is a simplified version of a rota spread sheet I’m working on. The full sheet would manage a rota over the course of a year for around 30 staff.

It’s not particularly graceful; however the way it should work is that in the ‘rota’ sheet A1-C10 lists each person for each day at work. If a person is not due in work that day someone will delete the user from the list. (This is somewhat automated but is simplified for the purposes of this question).

Using the formula you previously assisted with in another thread, a list is then produced of everyone working on a particular day in A15-C22 which automatically updates and removes blank cells.

This dynamic list is then copied to the ‘duties’ sheet in B15-D22. There is then a dropdown list using the dynamic list to assign each person to each role for each day.

My question is, is there a way to only allow a person to be assigned to one role each day? Could they be removed from the dropdown list so once they are picked for a role, they disappear from the list and can’t be selected again for that day?

I’ve seen this sort of thing done, however the methods I’ve seen use a second or third row of cells for referencing, which my sheet doesn’t have the room for.

A workaround I thought of is in row 13, which checks for duplicates in the postings. Ideally I’d like a way to only allow the user to select a person once each day.

Any ideas greatly appreciated. Thanks