shift work calendar (data export issues)

%3CLINGO-SUB%20id%3D%22lingo-sub-1621868%22%20slang%3D%22en-US%22%3Eshift%20work%20calendar%20(data%20export%20issues)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621868%22%20slang%3D%22en-US%22%3E%3CP%3EHas%20anyone%20used%20the%20shift%20work%20calendar%20for%20scheduling%20employee%20shifts%3F%20For%20some%20reason%20the%20template%20only%20comes%20with%203%20jobs%20attached%2C%20i%20was%20able%20to%20add%20more%20jobs%20in%20the%20job%20details%20area%20but%20cannot%20figure%20out%20how%20to%20transpose%20them%20into%20the%20calendar%20itself.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1621868%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622059%22%20slang%3D%22en-US%22%3ERe%3A%20shift%20work%20calendar%20(data%20export%20issues)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F776349%22%20target%3D%22_blank%22%3E%40alanna880%3C%2FA%3E%26nbsp%3BHad%20a%20brief%20look%20at%20the%20template.%20It's%20a%20massive%20collection%20of%20named%20ranges%2C%20named%20formulae%2C%20conditional%20formatting%20and%20nested%20IF%20functions%20containing%20more%20than%20300%20characters.%20Thus%20not%20very%20easy%20to%20change.%20I%20have%20not%20discovered%20any%20feature%20that%20allows%20you%20to%20add%20jobs%20and%20update%20the%20calendar%20automatically.%20So%2C%20that's%20a%20manual%20job.%20And%20with%20hidden%20columns%20and%20headers%20switched%20off%2C%20the%20design%20of%20the%20schedule%20suggests%20that%20it%20should%20not%20be%20easy%20to%20change%20the%20structure%20of%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMake%20sure%20that%2C%20for%20every%20new%20job%2C%20you%20create%20all%20the%20named%20ranges%20similar%20to%20the%20first%20three%20jobs.%20Each%20Job%20has%207%20related%20named%20ranges.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-31%20at%2006.35.47.png%22%20style%3D%22width%3A%20157px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215501iAFA6FC1BBC896DD9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%202020-08-31%20at%2006.35.47.png%22%20alt%3D%22Screenshot%202020-08-31%20at%2006.35.47.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThen%2C%20insert%20rows%20for%20each%20new%20job%20in%20the%20calendar%20sheet%20for%20every%20month.%20Copy%20all%20the%20formulae%20for%20Job%203%20in%20January%20to%20Job%204%2C%205%2C6%2C%20etc.%20for%20January%20and%20change%20all%20the%20hard%20coded%20named%20ranges%20that%20refer%20to%20Job%203%20so%20that%20they%20point%20to%20Job%204%2C%205%2C%206%20etc..%20Don't%20forget%20the%20row%20headers%20in%20column%20B!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-31%20at%2006.45.55.png%22%20style%3D%22width%3A%20922px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215502i450AA4AAC9B4B853%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%202020-08-31%20at%2006.45.55.png%22%20alt%3D%22Screenshot%202020-08-31%20at%2006.45.55.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOnce%20you%20have%20done%20that%2C%20you%20should%20be%20able%20to%20copy%20the%20new%20rows%20for%20January%20into%20the%20months%20below.%20Hopefully%2C%20it%20works%20as%20intended.%20Good%20luck!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Has anyone used the shift work calendar for scheduling employee shifts? For some reason the template only comes with 3 jobs attached, i was able to add more jobs in the job details area but cannot figure out how to transpose them into the calendar itself. 

Thanks in advance

1 Reply
Highlighted

@alanna880 Had a brief look at the template. It's a massive collection of named ranges, named formulae, conditional formatting and nested IF functions containing more than 300 characters. Thus not very easy to change. I have not discovered any feature that allows you to add jobs and update the calendar automatically. So, that's a manual job. And with hidden columns and headers switched off, the design of the schedule suggests that it should not be easy to change the structure of it.

 

Make sure that, for every new job, you create all the named ranges similar to the first three jobs. Each Job has 7 related named ranges. 

Screenshot 2020-08-31 at 06.35.47.png

Then, insert rows for each new job in the calendar sheet for every month. Copy all the formulae for Job 3 in January to Job 4, 5,6, etc. for January and change all the hard coded named ranges that refer to Job 3 so that they point to Job 4, 5, 6 etc.. Don't forget the row headers in column B!

Screenshot 2020-08-31 at 06.45.55.png

Once you have done that, you should be able to copy the new rows for January into the months below. Hopefully, it works as intended. Good luck!