Excel Help

Question

Hello!&nbsp;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...&nbsp;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...&nbsp;&nbsp;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...&nbsp;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?&nbsp;I am using Microsoft 365 for Mac v.16.54I can if it helps add the workbook to this thread? Sorry! It's been a very long time since I've used excel!

hansvogelaar · Answer

rhydianelewis&nbsp;
It would help if you attached a copy of the workbook.

rhydianelewis · Answer

HansVogelaar&nbsp;Hello, sorry I've attached it here... I wasn't sure what was best to do...

hansvogelaar · Answer

rhydianelewis&nbsp;
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?

rhydianelewis · Answer

HansVogelaar&nbsp;ahh Macros! Makes sense now! Thank you!&nbsp;&nbsp;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&nbsp;

hansvogelaar · Answer

rhydianelewis&nbsp;
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.

Forum Discussion

Excel Help

5 Replies

Resources