Nov 17 2021 10:30 AM
Hello!
I am trying to streamline my scheduling and timesheets through Excel, however I am unsure on if what I am trying to achieve is actually achievable...
Firstly, I've got a table with cells having drop down options to select what shift someone will be working, however I want a message box to pop up if I have added 2 people on the same shift at the same time and to also pop up should a shift not have someone allocated to it... I've tried using the =IF formula, but not sure how to finish the forumla to give the response I need...
Secondly, I would like a drop down cell to change colour when a certain option is selected... I tried using conditional formatting but again, I am unsure how to complete the formula...
And Finally... I then have a series of pages to follow which I am hoping to auto populate from Page 1 with hours... How would I go about this?
I am using Microsoft 365 for Mac v.16.54
I can if it helps add the workbook to this thread? Sorry! It's been a very long time since I've used excel!
Nov 17 2021 11:53 AM
It would help if you attached a copy of the workbook.
Nov 18 2021 05:21 AM
@HansVogelaar Hello, sorry I've attached it here... I wasn't sure what was best to do...
Nov 18 2021 06:03 AM
See the attached version (now a macro-enabled workbook, so you'll have to allow macros when you open it).
To view the code, right-click the sheet tab of the Rota sheet and select View Code from the context menu.
Can you explain how hours should be calculated?
Nov 19 2021 06:10 AM
@HansVogelaar ahh Macros! Makes sense now! Thank you!
Hours is still the bit I am trying to figure out... What I want to do is if for example someone is rotared in for a stage shift then their hours are 5.5hours and it automatically inserts that into the time sheet
Nov 19 2021 06:38 AM
You'd have to create a table on the same sheet or another sheet in the same workbook that lists the hours for each type of shift.
In the attached version, I have created such a table, with random hours, on Sheet3. I named it Hours. You should edit it as needed.
The TimeSheet1 sheet uses VLOOKUP to return the hours corresponding to the shift types selected on the Rota sheet.