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
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
Hi James
Here is my version.
You can copy the column in DUTIES and add as a new dates.
You will also need to add the staff on duty in ROTA.
You can hide the rows (row 14 onwards) with the formulas.
I would suggest you create a file for each month instead of the whole year as the array formula can be slow if there are too many columns.
Hope this helps.
Sunny
(Excel 2019)
Thank you so much for your replies, this has really helped me get to grips with this issue.
I'm using the suggestions in the sheet now and hope to have it working ASAP.
You guys are the greatest,
James
