Assistance creating work calendar

[amb13]

Good afternoon.

I am building a calendar for a friend that will put his three technicians on an on-call rotation weekly. I'm new to this, but enjoy tackling creative projects like this, however, I'm stumped how to add a technician for each week so they'll stay on rotation Monday through Sunday and then Tech #2 will take on-call Monday through Sunday and so on.

On my excel form I designed a 2 line calendar, one for the date and one for the technicians name. I color formatted previous and future monthly dates to be white out so I'd like to keep that same format for the technician (I think I have that formatted correctly)

Any help is appreciated. Thank you

[NewYears1978]

I messed with it a long time unable to make it work with using the dropdowns. I am sure someone better could create one that works. I could only make it work if you show all the calendars at once, which you probably don't want.
I made a dynamic one that lets you change the order of the people based on the rotation start date and let's you dynamically change those tech names if you needed to later, etc. It's not complete. I figure this WON'T work for you but it was sure fun.
I used a default template I found for a "Shift" calendar and heavily modified it to allow the names and some other stuff.

I could share it if you think it would be useful but if not, no biggy, I enjoyed working on it and learning haha

I can share it if you want to look at it even though it's not quite what you had. I'll attach a photo.

[amb13]

I messed with it a long time unable to make it work with using the dropdowns. I am sure someone better could create one that works. I could only make it work if you show all the calendars at once, which you probably don't want.

Thank you NewYears for taking a crack at it. I'm new at this and dabble in it in my free time. I should take it more seriosuly because I kind of enjoy it. What im attempting might not even be possible?

When i get home ill take a look at your sample.

Again, thank you for taking some time and reapinding.

[NewYears1978]

I am sure what you are doing IS POSSIBLE. It was just out of my scope.
The one I have does the same thing basically you can still change years, and all that, it's just that it has them all on one screen. Most the examples I found online were like this also.
With yours, you would probably need a data list with all the possible dates and would have to set the techs on it, then have the calendar pull it from there. It would work but be hard to change later if something changed (like a tech fired and new one hired, or something).

I may look at it again for fun. If you want I can upload this one I am working on just for giggles if you wanna see how it looks.

Roatation patterns are a common thing in the industry you use the pattern to setup the work week (this means if one day he decided the on-call techs were going to be every 3 days instead of all week, you can chanage the pattern)

Basically for 7 days on the pattern is like 111111122222223333333 (assuming techs are labeled 1, 2 and 3) but say you wanted 3 day rotation you could use 111222333 in this form to easily change the whole calendar and names associated.

Also, I can probably modify it to only show the selected month - I will look into that.

[NewYears1978]

I've got it how you want it now I believe - I will upload it once I fix some code. The helper stuff on the right will be moved to hidden tabs or whatnot, I just have it there for making the stuff work.

[NewYears1978]

Here's the sample - sorry for all the posts, I was going to remove the old posts and combine, but didn't realize I cannot delete my old posts..oops.

I can clean this up if you like it and think it will work. There's a hidden tab called variables that must remain. There are several named ranges and a one macro, that autoruns when required (only when you make changes to settings or the dropdown. The macro simply updates the names on the calendar and the colors.
I put in an explanation of the Rotation Pattern. Colors can be changed via Conditional Formatting, the header color (where the date is) can be turned off just using the dropdown.

Hopefully this helps..was fun to work with - let me know what you think. I made this in Excel 2016 so hopefully it works in other versions (or that you or your friend have 2016)

[amb13]

Hello.
First and foremost thank you again for replying and assistance.

I haven't had the chance to open these and look yet, but I will soon. I'll provide feedback when I do.

I didnt want to leave you hanging, especially when you're going out of your way to assist me.

Edit: i was able to look via my phone (at dr appointment )

[NewYears1978]

Hello.
First and foremost thank you again for replying and assistance.

I haven't had the chance to open these and look yet, but I will soon. I'll provide feedback when I do.

I didnt want to leave you hanging, especially when you're going out of your way to assist me.

No worries. I would ignore all my posts except the last one with the sample file. The sample file is a lot diff than all the images I posted. It's a more "completed" version that might work for you as is.

Haha Look forward to hearing what you think! Thanks for the follow-up.

[amb13]

Good morning, New Years.

I was looking at the Calendar Sunday before the superbowl and I absolutely love the layout. Setting M-F is easier to read technician schedules. The only thing that doesn't change is the technicians name in the calendar when I change them in the list to the right of the calendar. Am I missing something? I am going to study your design to learn everything I can. Again thank you!

[NewYears1978]

Good morning, New Years.

I was looking at the Calendar Sunday before the superbowl and I absolutely love the layout. Setting M-F is easier to read technician schedules. The only thing that doesn't change is the technicians name in the calendar when I change them in the list to the right of the calendar. Am I missing something? I am going to study your design to learn everything I can. Again thank you!

It should change their names when you change them. If not try changing the month and then change the month back and it should.

What version of Excel do you have? There may be a different method of Worksheet Change in your version than mine. 2007+ use a change event on the sheet while earlier versions run in a module, if I am not mistaken. Is yours 2003 or older?

Edit:
I checked and they are changing for me, please let me know what version of Excel you have. Also make sure you did click Enable macros when you opened the file and save it. Macros required to update the names after making a change.
If you change the pattern doe the names rearrange and colors change? (like change 111111122222223333333 to 123 and see what happens..if no change then you must have a diff Excel version or macros are not enabled)

You can also try to manually run the macro hit F8 and run the update macro.

[amb13]

NewYears1983:

I am using Microsoft Office Professional Plus 2019.
Version 2301.

I could see if it'll work at home, but I think I might have the same Excel version. I was primarily using the work version because it is more likely compatible with his version since its "work related"

[Pete_UK]

Take a look at this thread from about 10 years ago:

https://www.excelforum.com/excel-gen...t-pattern.html

It gives another way of presenting the data.

Hope this helps.

Pete

[NewYears1978]

NewYears1983:

I am using Microsoft Office Professional Plus 2019.
Version 2301.

I could see if it'll work at home, but I think I might have the same Excel version. I was primarily using the work version because it is more likely compatible with his version since its "work related"

Hmmm - it should work, I think. Are macros enabled?

Can you change the names then hit ALT-F8 and run the macro mUpdateCalendar and see if that updates the names? Trying to determine if my macro is not working or the Worksheet_Change that runs the macro is not working.

For me it changes when I changed anything, the Year, Month, Tech names, or any Calendar Settings.


See here for intended behavior (Animated GIF):
https://i.imgur.com/XTyM5B8.gif

It could also be that Excel 2019 uses different VBA for color/formatting backgrounds, which is how I am matching the names. I have 2019 at home I can test it on also, I will try later, I'll just have to install 2019.

Oh also, it could be blocking since it's a macro file from somewhere else. You would have to right click the file and choose properties and then click unblock. It even does that to me when I open it from home instead of work.

[amb13]

NewYears:

The Macros was the issue. I thought they were enabled, but I realized after taking a second look I had to save the file, reopen it, to enable the macros. That was an oversight on my part as I thought I enabled everything.

This looks amazing. You did a great job on this! I am going to study this and create various calendars to learn more.

Thank you again.

[NewYears1978]

Great! I hope it works out for you, if you need anything adjusted or changed let me know