Excel Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2976754%22%20slang%3D%22en-US%22%3EExcel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2976754%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20streamline%20my%20scheduling%20and%20timesheets%20through%20Excel%2C%20however%20I%20am%20unsure%20on%20if%20what%20I%20am%20trying%20to%20achieve%20is%20actually%20achievable...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirstly%2C%20I've%20got%20a%20table%20with%20cells%20having%20drop%20down%20options%20to%20select%20what%20shift%20someone%20will%20be%20working%2C%20however%20I%20want%20a%20message%20box%20to%20pop%20up%20if%20I%20have%20added%202%20people%20on%20the%20same%20shift%20at%20the%20same%20time%20and%20to%20also%20pop%20up%20should%20a%20shift%20not%20have%20someone%20allocated%20to%20it...%20I've%20tried%20using%20the%20%3DIF%20formula%2C%20but%20not%20sure%20how%20to%20finish%20the%20forumla%20to%20give%20the%20response%20I%20need...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecondly%2C%20I%20would%20like%20a%20drop%20down%20cell%20to%20change%20colour%20when%20a%20certain%20option%20is%20selected...%20I%20tried%20using%20conditional%20formatting%20but%20again%2C%20I%20am%20unsure%20how%20to%20complete%20the%20formula...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20Finally...%20I%20then%20have%20a%20series%20of%20pages%20to%20follow%20which%20I%20am%20hoping%20to%20auto%20populate%20from%20Page%201%20with%20hours...%20How%20would%20I%20go%20about%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Microsoft%20365%20for%20Mac%20v.16.54%3CBR%20%2F%3EI%20can%20if%20it%20helps%20add%20the%20workbook%20to%20this%20thread%3F%20Sorry!%20It's%20been%20a%20very%20long%20time%20since%20I've%20used%20excel!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2976754%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2977401%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2977401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1221839%22%20target%3D%22_blank%22%3E%40rhydianelewis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20would%20help%20if%20you%20attached%20a%20copy%20of%20the%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980253%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BHello%2C%20sorry%20I've%20attached%20it%20here...%20I%20wasn't%20sure%20what%20was%20best%20to%20do...%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.