Converting excel data into a printable calendar template

[tammyangel]

My work runs about 200 events per year.

These are listed in an excel spreadsheet.

However they also have a word template which has the layout of an annual calendar.

This means there are months across the top, and dates down the left hand side (like those big annual calendars you see on a noticeboard).

Everytime they add an event or edit an event, it has to be done in the excel spreadsheet manually, and then go into the word spreadsheet manually.

I am wondering if there is a way to update it in excel and it magically links to a template (or app) that has the look of a wall calendar.

It seems there is a lot double handling and opportunity to make mistakes.

Thanks

Tammy

[#2]

@tammyangel Perhaps you are willing and able to move away from Word, because you could do all within Excel, with Power Query / Power Pivot (provided you are working on a PC, not a Mac).

The attached workbook contains an example that demonstrates what's possible. Sheet1 contains the Events listing (Date, Event) and Sheet2 has a (power) pivot table with the Annual Calendar view you described. All don in a quick-and-dirty manner and most likely it needs to be adapted to your real situation.

Change the events listing (change a date, add, delete, rename), and refresh the pivot table. That's it! 

Edit: Added another file ("tammyangel-2.xlsx) . Power Query only. Personally I prefer that one.

[#3]

@Riny_van_Eekelen

how do I create this file please?

could you just give me a few pointers on how I start it?
Thanks

[#4]

@learn700 Which file do you mean? This is an old thread. I need to refresh my memory.

[#5]

@Riny_van_Eekelen sorry this is the first I have used this community. It's the file you have attached in this thread. 

I have a long list of milestones / events throughout the year in a list in excel (date and process), I wanted to put that into a calendar view to make it easier to see when all our deliverables, budget, etc are throughout the year. 

Thanks

[#6]

@learn700 Are you familiar with Power Query / Power Pivot? It's required to follow the steps taken in either of the files. I'm not able to go into detail now (on a Mac) and soon closing down for the night.

[#7]

@Riny_van_Eekelen no worries. I have never used power pivot and feel like I have been stuck in time with Excel as this power world is new to me!

I'm just trying to work out from the basics using the excel help bit and learn it as I don't know anything about it to be able to follow the steps. But it sounds pretty amazing all these functionalities!

[#8]

@Riny_van_Eekelen This was really helpful!  Thanks.  

Let's add a few more levels of complexity...

1. Let's use the previous example of 200+ events.  Is it possible to add an event type to the data?  Without it being part of the event name? 

2. I understand that the original question was to format the calendar to be the large yearly calendar.  Is it possible to format the pivot table to look like a 'regular' calendar?  

Thanks in advance,

[#9]

@Katrina Thompson 

1) Yes, that is possible. How would you want to use the event type in the calendar?

2) How would a "regular" calendar look like for you? 

[#10]

@Riny_van_Eekelen 

Thanks for the quick reply.   

In my use case, I'm collecting a list of workouts related to different running races.  I'm training for several races and each one has a different schedule.  I'm trying to consolidate all of those in one place.  

I'm attaching a spreadsheet based off your original reply. 

[#11]

@Katrina Thompson Then it's perhaps better you go for an other solution. I while ago I tweaked a rather poorly designed calendar template to do just what you have in mind. Originally a student assignment planner, but now it's a "Berlin Marathon" planner. See attached.

[#12]

@Riny_van_Eekelen Thanks!

So, this is a different solution - not using Power Query?  Correct?  

[#13]

@Katrina Thompson Correct. Believe this one is easier, because you want the monthly view.

[#14]

@Riny_van_Eekelen Thank you for this. I have modified it for my use, but am just wondering if you could share any tips on how you created it. Is it all excel filters, or does it use any power queries etc? As I would like to understand it a bit better in case I need to make further edits.

[#15]

@Dearbhla1690 That's a broad question. What exactly are you referring the to? The attachment to my previous post? If so, that one does not use Power Query. My modification to the template was to replace a VLOOKUP that returned the words "Assignment due" if it found something for that date in the event list. Quite useless.

With TEXTJOIN and FILTER, I could at least display the descriptions of all events at a particular date. Not really much tips I can give unless you ask specific questions. 

[#16]

Hi! I am wondering if there is a way to only show one "Type" on the calendar. For example, on the first sheet, you can apply a filter that only displays "Walk." But is there a way to only display "Walk" on the calendars on Sheets 2 and 3?

[#17]

@al_fa1922 There are no drop-downs in this template, other than the month and week. So, what do you mean? The filter button in Events show Race, Run Walk just because these words were entered in column D in the first sheet.

The Month and Week sheets just concatenate Type and Distance from the Events sheet in the calendar sheets.

[#18]

@Riny_van_Eekelen Yes, the Berlin mara attachement.

I think I have it doing what I want so many thanks

[#19]

Yes, my apologies. I realized that after I sent the message. But, let's say I want to just view my "Walk" schedule on the calendar. Is there a way to filter that or change the concentration of the month and week sheets?

[#20]

@al_fa1922 You are trying to add functionality to customised template. What you have in mind. If you only want ro show "Walk" why not create a calendar with only "Walk" entries?