Excel Help

Copper Contributor

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!

5 Replies

@rhydianelewis 

It would help if you attached a copy of the workbook.

@Hans Vogelaar Hello, sorry I've attached it here... I wasn't sure what was best to do...

@rhydianelewis 

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?

@Hans Vogelaar 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

 

@rhydianelewis 

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.