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

**Carrie_Smattick** · 2013-06-23, 09:51 PM

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-prog...55#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.

weekend_working.xlsx

Thanks so much.

**JeffreyWeir** · 2013-06-23, 11:48 PM

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

**Carrie_Smattick** · 2013-06-24, 12:08 AM

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.

**JeffreyWeir** · 2013-06-24, 02:25 AM

Can you post some of the exact text so that I can search for it and post a link on your behalf?

**JeffreyWeir** · 2013-06-24, 02:33 AM

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.

**Carrie_Smattick** · 2013-06-24, 08:32 AM

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.

**Carrie_Smattick** · 2013-06-24, 05:32 PM

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. 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).

**JeffreyWeir** · 2013-06-25, 12:46 AM

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?

**Carrie_Smattick** · 2013-06-25, 08:03 AM

Oh of course, yes. I hadn't even thought about that.

**Carrie_Smattick** · 2013-06-25, 08:32 AM

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.