Allocate duties by availability and role

[shiftsailor]

I've been using Excel for a long time now in my work (teacher - I manage the assessment databases and tracking) but would like to be able to use it to allocate volunteer duties for a sports club and am not sure where to start. The complication is that most volunteers are capable of carrying out more than one role but they should only be assigned one per day. Ideally, each volunteer would be given a mixture of duties over a number of weeks e.g. helm one week, OOD another.

Information:

1. there are 3 sheets; column A of the 'roles' and 'availability' sheets contains volunteer IDs
2. in the 'roles' sheet, y = yes, this volunteer can perform this duty (empty cell = do not allocate this duty)
3. in the 'availability' sheet, x = not available (empty cell = available)
4. the 'rota' sheet is the destination for the allocated duties - volunteer IDs should be what goes in the currently empty cells
5. each date needs 3 different volunteers to be helm and 3 more to be crew. There is no significance to the helm/crew number, that's just a label so we can match volunteers with the correct kit.

Ideally, the rota sheet would be semi-automatically populated once availability data for each volunteer is updated.

I have set up a sample file containing three sheets and would be extremely grateful to anyone who is able to point me in the direction of how to get started with this. I realise my sheets may not have been set up in the best way and that I may have to redo them, hence only including a couple of months.

 

[eibi]

shiftsailor,

I don't think this solution is perfect, by any means, but if you really are just wanting someone to 'point you in the right direction'...

I'd suggest multiplying the Role x Availability x Volunteer ID and setting a randomizer to select from them. There's always a chance that you'll get a double assignment, but you can go back and check those and adjust manually. I think they'll be the exception rather than the norm.

Of course, with a RANDBETWEEN function in your formula, the sheet will recalculate every time you open it, so you'll want to take a screenshot or print to PDF, or copy/paste values only to another location if you want to save/edit the assignments from week to week.

Hope this is the direction you were needing.

 

[shiftsailor]

This is exactly the direction I needed, thank you! It's a lot less messy than what I'd come up with and results in fewer duplicates within each column. I'm struggling to expand the range the rota data uses from the roles and availability sheets which is odd. I usually find I am able to expand the range manually if need be but that returns an error. At present I need it to draw from 56 rows although once the season begins, I anticipate needing to further increase the rows in the roles and availability sheets. Where am I going wrong?

 

[vletm]

shiftsailor
You could check this sample too.
Press [ Do It ]-button
... there are two modes; if cell B1's font is bold then 'animation' otherways 'quicker'.
Your file has 'some feature' which would modify ... but later those ... if You're interesting in.
... including that You can add delete Your data - no need to take care any formulas.

 

[eibi]

Great! Glad it's helpful!

I'm struggling to expand the range the rota data uses from the roles and availability sheets which is odd. I usually find I am able to expand the range manually if need be but that returns an error. At present I need it to draw from 56 rows although once the season begins, I anticipate needing to further increase the rows in the roles and availability sheets. Where am I going wrong?

I suggest setting up Named Ranges in the Roles Sheet and the Availability Sheet. They can initially be defined as 56 rows, and updated as needed in the Name Manager.

By doing this, your Rota sheet can simply refer to the Named Ranges rather than specific cell references on those other sheets -- and when you add content to the Roles or Availability sheets, you won't have to edit the formula in the Rota sheet.

 

[vletm]

shiftsailor
My sample with 'Minor' modifications like:
> clearing a counter and results as want
>> ( a counter - below [ Do It ] - take cares that allocation would be as same as possible for everyone, as long time as it has kept unclear )
> more clear way to as many more roles need to rota-sheet ( add to lines of code )
> add as many 'dates' as need (still those are as texts)