Help needed for a weekend rota - not quite so straightforward.

Carrie_Smattick

New member
Joined
Jun 23, 2013
Messages
16
Reaction score
0
Points
0
Hi, all.

I really hope someone can help with this. I have posted in a few forums and I've had no responses, so I'm not sure if what I'm asking for is possible or not.

http://www.excelforum.com/excel-pro...-ranges-then-error.html?p=3288255#post3288255

I am creating a weekend working rota. We have around 30 staff covering weekend working. They work on 4 different contracts, but can only work on any particular contract if they have done so in the last three months (else they require refresher training). If a name is entered for a contract that the worker has not covered in the last three months, I need an error that says that name cannot be entered.

The workers also have the option to be paid for the day they work, or they can take a day in lieu. I need to count up the amount of days taken in lieu each month so we can see how many lost days we have during the week over each month.

I hope I am making sense.

I have attached a sample spreadsheet but I've no idea if I would need a completely different layout to get the results I am asking for. I would really appreciate some help with this as the weekend working has already begun.

View attachment weekend_working.xlsx

Thanks so much.
 
Last edited by a moderator:
Hi Carrie. Note that cross-posting is generally frowned upon, although in cases like this where you haven't received a reply after a prudent period of time you may do so, provided you supply all links to the duplicate question on the other forums. Otherwise you may well be wasting multiple volunteers time on multiple forums. For instance, I see this is also at Ozgrid at http://www.ozgrid.com/forum/showthread.php?t=179896 , and was posted pretty much the same time you posted here.

Can you post links here please, and if it hasn't been answered I'll take a look elsewhere. Also you need to post links on all those other forums too.

Cheers
 
My apologies. I am not used to these forums, and didn't realise I shouldn't cross-post. The other forums were some time ago, before we were adding the option of workers taking pay or day in lieu, and I can't remember where I posted them. The recent one was 13th June on Excelforumdotcom. I can't post a link yet as my post count is too low.
 
Can you post some of the exact text so that I can search for it and post a link on your behalf?
 
If a name is entered for a contract that the worker has not covered in the last three months, I need an error that says that name cannot be entered. Where do they enter a name? Column A? Or are they in fact choosing whether to put ContractX_Pay or ContractX_Day in the row against their name for a particular date? I.e. what exactly do they enter, and where do they enter it?

I need to count up the amount of days taken in lieu each month so we can see how many lost days we have during the week over each month. Where do you want this info returned? If you need a new column, can you amend your sample file to suit, then upload again? Then I'll take a look for you.
 
Thanks for looking at this for me, Jeffrey. I really appreciate it.

I put the link on ozgrid last night so you can get the link from there to save searching, and post here (thanks for that too).

I am about to leave for work but I will make the adjustments to the spreadsheet this morning and repost for you.

I'll put some real data in so I can hopefully explain it better.
 
Sorry, I didn't explain it very well. It is the contract they enter, e.g. Contract1_Pay or Contract2_Day in the row against their name for a particular date.

I have put some actual data in there - I hope this helps.
View attachment weekend_working.xlsx

I put a mock error where it would be really handy if a worker picks a contract they haven't worked on for three months, the error pops up and won't allow them to continue (but still allows them to select a different contract that they worked on in the last three months).
 
You probably also need a 'refresher training' option in your picklist too. Otherwise it will never let them pick an option, even if they have had refresher training. Can you confirm this?
 
I have thought of something else, which hopefully won't make any difference, but thought I should check with you...

Our Accounts department currently use a very basic spreadsheet where they highlight the cells of those confirmed to have worked (as some workers can cancel, call in sick, or switch last minute). They would just be highlighting the cells where the the contract is entered.

I am hoping we can all work from one worksheet to save duplication/wasted time.
 
So you want to easily retrofit my approach to their sheet? And do you want the code to check to see if a cell is highlighted or is that not really relevent to the code and more a case of being able to implement something new without upsetting HR's applecart?
 
They'll just use this sheet (as long as it won't interfere with your approach). It's not really relevant to my team whether HR sign it off or not, I just wanted you to know in case you were taking an approach where colours were involved. They will literally just highlight cells as they go through each month to pay those that actually worked (marking them off). They won't be coding anything, or require any coding. Their current spreadsheet is basically a table, and they manually update it frequently throughout the month, so I'm thinking they should save some time here sharing this sheet, rather than us working from separate sheets.
 
Okay that's simple enough. One more question. I note your data starts as at 1 June 2013. Because we don't have 3 months of data before this date, then we've got no way of checking for the first three months of that date range whether they've covered a particular contract in the last 3 months or not. So you really need to put the previous 3 months data in there as well (perhaps hidden to the left of 1 June).

Your thoughts? If that's a requirement, can you amend your spreadsheet to suit and upload it. Should only take me 30 minutes max to code it up once you've done that.
 
That's no problem, I'll source the data now and put it all in.

ooh it's exciting!
 
Sorry, it took a while getting on to the network at work. I have added all the data I can for the previous three months. They are all currently 'pay' as 'day' is a new trial - just in case you look at it and wonder why there are no days in there.
View attachment weekend_working.xlsx

I'm trying to think of anything I might have missed. We have around 20 new starters joining over the next couple of months. Will there be any issue just adding them to the bottom of the list as they join us? and can we lock the formulas so no one other than me can edit them? I have no option but to share the sheet as the workers' Line Managers add them to the sheet, and it has been known where someone comes along and accidentally deletes something, or decides to try something new - overwriting what you've done. I'll keep a master copy of course, but locking some cells would be very handy.
 
No problem. One potential issue. Above, I'd mentioned that you also need a dropdown or something setting out when they last had training in a particular contract. Instead of that, I think you need 4 columns - one for each contract type - where they just put the most recent date that they completed training for each contract type. Sound good?
 
Yeah, I think that could work. I was wondering how it might work in the drop-down. I've been playing about with different layouts, but I think I have looked at it for so long that I've stopped seeing anything that seemed like the best option. With the potential to type in the most recent training, we could have only the Managers update it once they provide the training (or my team in Quality), so that could work really well.
 
Carrie...this slipped off my radar, sorry. Just about to pick it up again. Before I do, have you revised the structure/layout of your file at all? IF you have, best you upload it so I can make the requisite changes to the final copy.
 
No problem at all - I know how busy you are. I haven't changed anything as I was concerned that you were working on it and would come back to me uploading another version.
 
Sorry, Jeffrey. I've been out of the country (unexpectedly) and had wifi that was completely useless.

Shall I change the layout? - I'm conscious that I might do something whilst you're doing something else, and don't want to waste your time.
 
Back
Top