No, leave as is. Was doing a bit on this last night, and changed some things. Should have something soon.
Help needed for a weekend rota - not quite so straightforward.
- Thread starter Carrie_Smattick
- Start date
Carrie_Smattick
New member
- Joined
- Jun 23, 2013
- Messages
- 16
- Reaction score
- 0
- Points
- 0
Thank you so much. I really appreciate it.
Okay, I think this should be a good start. View attachment Copy of weekend_working-3.xlsm
Note that there are additional 'Last Training Date' columns in rows B to D. Currently only dates for Andrew Fawcett are there. Note that the one for Shell is dated 29/12/2013, whereas the others are much earlier. So if you select Shell from the end columns, you won't get any error message, because it hasn't been three months sinces last training. But if you select the others, you will get a message, and Excel will undo the selection.
Also note that I’ve taken out the columns “Total lost days during June” etc out of your original worksheet. If you explain what you are trying to do with the ‘Total Lost Days’ then I can help you find a better way to implement this.
I’ve also unmerged any cells that were merged. Merged cells are a recipe for disaster – especially when there is VBA code. As a rule I NEVER merge cells, and instead use the Center Across Selection option that pretty much looks the same. See http://datapigtechnologies.com/blog/index.php/mocking-the-merge-center-icon/
And I’ve turned your data and validation lists into Excel Tables using the the Ctrl + T shortcut. These are available in Excel 2007 or later, and are basically a list of dynamic named ranges that Excel expands or contracts behind the scenes. I use See http://chandoo.org/wp/excel-tutorial/using-tables/ or http://office.microsoft.com/en-nz/excel-help/overview-of-excel-tables-HA010048546.aspx or do a google search for more info.
Have a play, then let me know what enhancements you need and what extra functionality, and we’ll bolt it on.
Note that there are additional 'Last Training Date' columns in rows B to D. Currently only dates for Andrew Fawcett are there. Note that the one for Shell is dated 29/12/2013, whereas the others are much earlier. So if you select Shell from the end columns, you won't get any error message, because it hasn't been three months sinces last training. But if you select the others, you will get a message, and Excel will undo the selection.
Also note that I’ve taken out the columns “Total lost days during June” etc out of your original worksheet. If you explain what you are trying to do with the ‘Total Lost Days’ then I can help you find a better way to implement this.
I’ve also unmerged any cells that were merged. Merged cells are a recipe for disaster – especially when there is VBA code. As a rule I NEVER merge cells, and instead use the Center Across Selection option that pretty much looks the same. See http://datapigtechnologies.com/blog/index.php/mocking-the-merge-center-icon/
And I’ve turned your data and validation lists into Excel Tables using the the Ctrl + T shortcut. These are available in Excel 2007 or later, and are basically a list of dynamic named ranges that Excel expands or contracts behind the scenes. I use See http://chandoo.org/wp/excel-tutorial/using-tables/ or http://office.microsoft.com/en-nz/excel-help/overview-of-excel-tables-HA010048546.aspx or do a google search for more info.
Have a play, then let me know what enhancements you need and what extra functionality, and we’ll bolt it on.
Last edited:
Carrie_Smattick
New member
- Joined
- Jun 23, 2013
- Messages
- 16
- Reaction score
- 0
- Points
- 0
You are amazing! Thank you!
The total lost days thing is because we are just trialling the 'day' option where staff usually just get paid, and I need to check how many days of analysis are lost by the Analysts taking a day in lieu, rather than doing the weekend day as a sixth day of the week. Does that make sense? I feel like everything I write here makes no sense at all - except to me.
The total lost days thing is because we are just trialling the 'day' option where staff usually just get paid, and I need to check how many days of analysis are lost by the Analysts taking a day in lieu, rather than doing the weekend day as a sixth day of the week. Does that make sense? I feel like everything I write here makes no sense at all - except to me.
Carrie_Smattick
New member
- Joined
- Jun 23, 2013
- Messages
- 16
- Reaction score
- 0
- Points
- 0
oops, one other thing... the contracts at the beginning: Shell UK and International are two separate contracts, is it easy to add another in so I have four in total?
Carrie_Smattick
New member
- Joined
- Jun 23, 2013
- Messages
- 16
- Reaction score
- 0
- Points
- 0
I am just testing out by adding in the workers for the weekend just gone, and it works great 
)
One question, if a worker has training that is longer than three months ago, but they have been working regularly on the contract, will the worksheet take that into account? e.g. WorkerA receives training for ContractA on 1/1/13, and works almost every weekend on Contract A. On 2/4/13, that worker won't require refresher training as they are experienced on the contract, but will we still get an error as they were trained over three months ago?
)One question, if a worker has training that is longer than three months ago, but they have been working regularly on the contract, will the worksheet take that into account? e.g. WorkerA receives training for ContractA on 1/1/13, and works almost every weekend on Contract A. On 2/4/13, that worker won't require refresher training as they are experienced on the contract, but will we still get an error as they were trained over three months ago?