Auto-populate a weekly calendar with several "lanes" of activities

[lpf24]

Hello all!

I'm writing to ask for some help creating a variation of the excel activities calendar I've seen on here originally by a super user named Pete!

In his original calendar, it is monthly where one can export a list of appointments from Outlook and paste them into an "activities" column by date. These activities then populate into a monthly calendar. It is a wonderful document that I am hoping to modify for my needs.

That said, my wish is to take this format and make it a weekly calendar. In addition, I'd like to find a way to have several categories of activities per day. For example, in a given week of the calendar we could have a day like Monday, which has 5 categories of activities. Within each category is the appointments of that type for the day. On the backend page of the excel file it would have 5 columns for each type of activity.

I've attached a very rough idea of what this might look like. Any help would be greatly appreciated! Thank you all!!!

Best,
Luke

[Pete_UK]

Looks fairly straightforward, but how do you intend to select the week? At the moment you have the month and year which can be chosen by drop-downs (as in the original), but I think it would be better to have a list of week-commencing dates (which could be Sunday or Monday), and to select from these.

How many activities of one particular category would you expect to have? At the moment you show up to 12 activities for each category, i.e. up to 60 for each day !! (Are you a slave-driver ??, or you just like to micro-manage ??) (Haha).

I'm just about to take a break now, but I'll check back later.

Pete

P.S. Thanks for the kind words.

[lpf24]

Ah, Pete! Great to hear from you. Let me explain a bit further. Yes, I essentially took your original template and made some modifications to show show the file would work--but to be honest I do not know how to make this happen.

This file would be used to integrate information from 5 separate sources of information into one master calendar source. The first activity section would be personal, day-to-day appointments which I think 12 has been a good number for. The other activities are important events from other areas of our business, it might make more sense to have each of these areas at say 5.

Would you be able to help me with this? I would be very, very grateful!

[Pete_UK]

Yes, I'll be able to put something together for you. I have some family visitors over the weekend, though, so it may not be until early next week before I get back to you.

Do you have any preference over week-commencing dates being a Sunday or a Monday?

It would also help if you could fill in a few dummy records in the Activities sheet, so I can see exactly the type of thing to expect.

It also occurs to me that instead of having 5 (or so) identical groups of columns for the activity type, there could be just one set along with a new column which indicates which activity the record relates to.

Pete

[popipipo]

Take a look at this

[AliGW]

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks.

[lpf24]

Yes, I'll be able to put something together for you. I have some family visitors over the weekend, though, so it may not be until early next week before I get back to you.

Do you have any preference over week-commencing dates being a Sunday or a Monday?

It would also help if you could fill in a few dummy records in the Activities sheet, so I can see exactly the type of thing to expect.

It also occurs to me that instead of having 5 (or so) identical groups of columns for the activity type, there could be just one set along with a new column which indicates which activity the record relates to.

Pete

Hi Pete! Thanks so much for your offer to assist--cannot thank you enough. I think starting on Monday would be my preference. Attached is a dummy file with some records for your reference. Regarding your idea to use on column, I think that would work also. However, I intend to export data from 5 different sources and then paste into the activity section. As such, I thought it might be faster to use different columns as opposed to categorizing different appointments--we are talking about a lot of data for each section! Of course, I defer to your expertise here.

A bonus would be the ability to apply some images in here automatically too, so, if an item is in the Company X section I would try to implement a logo next to those appointments.

Thanks again for all the help!

[lpf24]

Hi Willem! Thanks so much for adding into our conversation here. I think you are very much on the right track. This spreadsheet mirrors the work Pete has done in the past, and now I am trying to expand a bit so that I can pull in information from 5 calendars into one "master" calendar if you will!

[Pete_UK]

I've set up the calendar how I think you want to operate it.

You will notice in the Activities sheet that I have added formulae to each of the Ref columns. These have been copied down to row 1000, but you can copy them further if you need to. The data can be entered in any order.

In the Calendar sheet I have reduced the number of events for each activity area to 5 except for the first one, which I have left at 12. You can still select the month and year of interest using the drop-downs in M4 and M5, but I have added a new drop-down in cell O4 to enable you to select the week commencing date (based on Mondays). These are generated automatically in the cells below in column O, and start with the Monday in the previous month, to ensure that we pick up the first few days of the month selected.

I've added some test data, and all seems to be working fine, but test it out yourself and let me know how you get on.

Hope this helps.

Pete

[lpf24]

Pete! So sorry for the delay--I typed a reply DAYS ago. In checking this now, I see it hasn't posted--so sorry about that.

The file you've produced is amazing--exactly as I had imagined it would work. Incredible work! I cannot thank you enough!!

[Pete_UK]

Thanks for getting back to me - glad the file does what you wanted it to do.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete