• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rota allocation niggles

S

shiftsailor

New Member
Last year I posted on here and received some excellent support with my questions about how to use Excel to allocate duties to volunteers based on their availability and the roles they were qualified for. I have uploaded a dummy file to illustrate the couple of issues I would like to resolve.

1. from 4th June onwards on the rota tab, #N/A is returned in every cell for every role. I can't work out why the code won't extend beyond May!
2. how do I eliminate duplicates within a column on the rota?
3. is there a way to force Excel to more evenly allocate people across the dates so 'Claire' doesn't end up on duty every weekend just because she's available but Mike is never used when he's around?
4. the first name has been entered as 'no availability' - this is because no matter what I do, they end up put on weeks they are not available. This person is currently not available on any date and yet appears repeatedly across the season - what have I done wrong?!

I'd be extremely grateful for any help with these queries.
 

Attachments

  • Dummy duties 2022.xlsx
    32.8 KB · Views: 3
Sorry @vletm - I did test your sample but because I couldn't work out how the do it button worked, didn't end up using it! It's very clever and if I could fathom what was going on behind it so I could modify it if need be, it would be perfect! This time, I have asked volunteers to indicate the dates they are available (historically they've been asked for when they can't help) so it would be useful to be able to include that in any formula.
 
vletm said:
shiftsailor
Did You test that my sample ( Mar 21, 2021 )?
Click to expand...

I'm using your sample now but am getting a popup that tells me there's a missing connection between rota and roles. As a result, it is only returning values for the first date column. Please would you advise me re: how to edit the function of the do it button. I am unable to attach the file here as it contains people's names.
 
shiftsailor
I tested my sample file myself - for me it seems to work as well as one year ago.
Have You done something with it?
I cannot find any popups about a missing connection between rota and roles.
You could modify those people's eg to numbers.
I should do 99.999% guess ... if I even try to advise You to modify something.
... About 'Dates'
... There are none dates! There are texts, which looks like dates.
I tested it with Your newer file ... It gave something like below:
Screenshot 2022-03-10 at 15.46.18.png
 
Thank you for replying. I have modified the duty name so instead of CREW 1-3 there is CREW, AI and DI as this reflects what the rota needs. We don't currently have enough people to fulfil the AI and DI roles so some of those cells may need to be blank.
 

Attachments

  • Anon dummy duties 10th March 2022.xlsb
    32.1 KB · Views: 2
shiftsailor
There were challenges, if there are missing enough resources.
As well as - the layout is still so sensitive - because this is still sample.
I did some other minor modifications.
 

Attachments

  • Anon dummy duties 10th March 2022.xlsb
    34.1 KB · Views: 4
Thank you @vletm - that definitely works better than when I tried it! Volunteers expect to be on the rota for at least four weekends per season and those who have more availability are generally happy to do as many days as they are needed for otherwise the session has to be cancelled. What would I need to do behind the scenes of the 'do it' button to take account of this? Ideally, it would populate all the days unless there was no-one available who could perform that role.
 
shiftsailor
Do You run it animated or non-animated -mode?
You can select it from roles-sheet's cell B1 - if it's bold then it'll be animated.
There is a huge difference in 'speed' between those modes.
 
You must log in or register to reply here.
Back
Top