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]

Hi @Riny_van_Eekelen. Can you possibly please explain how you would turn the attached data spreadsheet into a monthly or annual calendar of events - like a monthly planner view not a list as it is now using power query/power pivot? Thank you :) 

[#3]

@tahnee_h That's a very broad question and not so easy to answer, but the basis for any scheduling would be a list of columns in the correct structure. In your case a table with columns for Date, Time, Location, Function Name, Function Type and Pax.

But that's going to be a lot of information to be fitted inside a calendar template like the one being discussed in this thread. I dare to say that it would be best to use an out of the box calendar system like the ones that are included in your e-mail system. I would never consider developing such a system in Excel myself.

[#4]

This kind of calendar is exactly what I generated for the time off calendar I was making. There's no doubt it takes work to produce but it can be done

[#5]

@Riny_van_Eekelen im jumping in this thread late, but have been reading along and this is just what i have been trying to work out. I work in a highschool and have a lot to do with calendars, have you ever created a spreadsheet that lists the events (assignments) and then can create a weekly, monthly or termly calendar but could also be filtered by faculty so they could print one?

[#6]

@jennyc1605 I guess it should be possible to add the faculty to this, but it would be yet another modification on top of others. It could get quite messy, I'm afraid. Do you already have the assignments in a format/structure that would fit the template you have been looking at?

[#7]

@jennyc1605 

There are quite a few steps to making the spreadsheet calendar you're looking for but it is doable. Subarrays, and filter functions. Depending on the layout you have in mind, depends on the amount of work it would take 

[#8]

@Riny_van_Eekelen 

So far you have been very helpful. I still can't adapt the table for my needs. Some of my needs are for a class name

Class name  | Date| Date| Date| etc. (up to 12 possible dates)

Is there a way to see the table on a normal calendar view or is the list the only way to use that raw data?

Thanks for your insight

[#9]

@RickJ80 Share a file please, because it's difficult to visualise how Class, Date, Date, etc will affect the calendar.

[#10]

@Riny_van_Eekelen  Something like this where I can assign a specific name under the first block. The 24 block would be where I enter in the specific class name.

24

Jan

1/8/2024

1/9/2024

1/10/2024

1/16/2024

1/17/2024

1/18/2024

1/22/2024

1/23/2024

1/24/2024

1/29/2024

1/30/2024

1/31/2024

24

Jan

1/22/2024

1/23/2024

1/24/2024

1/29/2024

1/30/2024

1/31/2024

2/5/2024

2/6/2024

2/7/2024

2/12/2024

2/13/2024

2/14/2024

24

Feb

2/5/2024

2/6/2024

2/7/2024

2/12/2024

2/13/2024

2/14/2024

2/20/2024

2/21/2024

2/22/2024

2/26/2024

2/27/2024

2/28/2024

[#11]

I couldn't figure out how to send a file

[#12]

@RickJ80 Save your file to Onedrive or similar and than copy/paste the link that gives access to it in your reply. Or send it to me via a direct message. Click on my 'name-tag', then Message. Now you can drag and drop a file in the appropriate place.

[#13]

@Riny_van_Eekelen 

Hi!  I stumbled upon this old post looking for a solution.  Seems your calendar "Berlin Mara" might work.  I downloaded, however it seems the link to the "assignment" sheet no longer exists.  I am assuming that's where I need to put my information?  Any help would be appreciated. 

Thank you for your time.

[#14]

@vdefilippo Yeah, the ASSIGNMENTS tab was renamed to EVENTS. You'll see a question about it a bit up in the thread.

And indeed, the EVENTS tab is where you enter whatever you want to see in the calendar.

[#15]

this is genius! It's exactly what I've been looking for. I'm pretty much a basic user of excel and I appreciate your contribution.

If you don't mind I have a few questions-

My intention is to use this as a vacation calendar. I see there is an "inconsistent formula" message in each bock. How do I fix that?

On thee week view- row 15 columns e, f, g all have "#VALUE!". How do I fix that?

[#16]

@vdefilippo "Inconsistent formula" is just a warning, you may ignore it. I don't see it as I turned off these background error checking messages quite irritating. Can't really tell why you get value errors in the week view. I would have to see the file. 

[#17]

Sorry- another question. How can I incorporate this into another workbook? I have a dashboard and I'd like it to be part of that sheet. I see a simple copy paste won't do it.

[#18]

@vdefilippo Asking is free, but we are dealing with a three sheet workbook with quite a bit complexity. Not something you can 'just put into another sheet'. Perhaps better that you open a new thread explaining your dashboard and that you want to integrate some sort of calendar view in it. Not sure that the calendar in this tread is a very good one. The template it's based on is, actually, totally useless. I just made it to become slightly less useless. :)

[#19]

My apologies.
I was able to get it into the sheet I need it to be on and it works! Thank you again! This is a wonderful tool! Have a great day. :)

[#20]

@Riny_van_Eekelen Well- I thought it would work- it did in excel. But I guess there's an issue when getting it into google sheets. I think it has something to do with the filters. I'll continue to see if I can figure it out.
Thanks.

[#21]

@vdefilippo Google sheets may resemble Excel but there are quite a lot of differences. But, I'd be surprised if you can get that template working in GS. Good luck!