Excel Forum

Dynamic dropdown list

James Brown

Active Member

Members

Forum Posts: 5

Member Since:
March 6, 2021

Offline

March 11, 2021 - 5:57 pm

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

See Answer

Lionel Baijot

Member

Members

Forum Posts: 126

Member Since:
September 9, 2020

Offline

March 12, 2021 - 3:41 pm

Hi James,

Here is the file with some adaptations (I give here the formulas in French because I am Belgian but they will adapt automatically when you open the file):

The list of people to choose from has been moved to J2. From this list, I created a custom range called 'All'. This is : =Duties!$J$3:$J$10.
Next, I have a list of choices that you have already made or will make. This range is called 'Choice' and is based on : =Duties!$B$2:$B$11.
And finally, I have column K which lets me know who hasn't been chosen yet. I use this formula in K3:
=SI(LIGNES($1:1)<=NBVAL(All)-NBVAL(Choice);INDEX(All;PETITE.VALEUR(SI((NB.SI(Choice;All)=0);LIGNE(INDIRECT("1:"&LIGNES(All))));LIGNES($1:1)));"").
This is a matrix formula to be validated with CTRL-SHIFT-ENTER.
This list is used to create the 'Rest' range which is
=DECALER(Duties!$K$3;;;SOMMEPROD(--(Duties!$K$3:$K$10<>""))).

Now you just have to open the file and make a choice in column B.

BR,

Lionel