How to auto populate events into calendar

I am working on trying to auto populate events from a table into calendars on other worksheets. I would like the event name pulled from the Event Column and plugged into the corresponding date on the calendar which is listed in the Date column in the table.

Is there an easy way to do this?

Thanks!

I've submitted lots of files to the forum to do this, so if you attach an example showing how your event data is laid out I will amend one to suit your circumstances.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

One of the main considerations is whether your events are only for a single day, or if you have a date range (with a start and end date) for each event. Another consideration is how many events may occur on any one particular day.

Anyway, attach an example file and I'll get back to you when I've had a look at it.

Hope this helps.

Pete

Hi Pete,

Yes, I found a thread from 2012 that had exactly what I was looking for. However, I was unable to deduce how to complete the desired formatting for myself. I believe that just a singular date would be fine and potentially up to five events per day would probably suffice. However, I would be very interested to know how to edit the document to adjust those parameters in the future. Additionally, would it be possible to populate both the Event #, as well as the Event into the calendar?

Thank you! I very much appreciate the help!

Madison

Do you have any events on a Saturday and/or Sunday? If not, I can make those columns narrower and the others for Monday to Friday wider in order to accommodate your long event titles.

The calendar file that I am working on will enable up to 10 events per day to be displayed. It contains only one monthly sheet - you can select the year and month of interest using drop-downs, so it is easy to get another month displayed. It also means that you can keep adding data for any year you like, so you don't have to have one file for each year (unless you prefer to operate that way).

I'll put some test data in before I send it back to you, so that you can see the overall effects more clearly.

Pete

There could potentially be events on Saturday and Sunday, so I do need those columns kept. And with regards to condensing the months to one sheet, I appreciate the effort to make it easier to navigate, but I'm not sure if that's the desired formatting my team would like. Is it possible to keep it as is? If you've already made the changes, no worries! I can check with them and see if the new formatting works for everyone.

Thank you!
Madison

You can just CTRL-drag the Calendar sheet to create another copy, rename it to the appropriate month, and then choose that month in the drop-down. You can do this 11 times if you wish to, so that you have 12 monthly views.

If you want to change the formatting on the Calendar sheet (colours, font size etc.) then it would be best to do all that first before copying the sheet.

I'm just about to send it to you - cup of coffee first, though.

Pete

Hi Madison,

In the attached file I've set up your Events table in columns D to I of the Events sheet, and added some test data (shown in red).

This sheet contains two columns of formulae (A and B) which have been copied down to row 1000, but could be copied down further if you have that much data. These columns can be hidden if you don't want them to distract from data entry. The first column picks up the date and a unique sequential number to cope with multiple events on the same day. The second one controls what gets displayed - basically the event number and the title of the event. Your event numbers don't have to be strictly sequential - you could use some code in there as shown in two of the examples. Basically, this sheet is for your data entry into the table, and everything else is automatic. You can enter data in any order.

In the Calendar sheet there are two cells used to control the month and year to be displayed, K5 and K6. The display adjusts automatically (and immediately) as these are changed. Up to 10 events can be displayed for each day, and there is an alternating background colour to make these a bit clearer. With a layout like this, i.e. Sunday to Saturday across the sheet, then a 30-day month which starts on a Saturday, or a 31-day month which starts on a Friday or Saturday, will require 6 weeks to be displayed, with one or two "orphan" days appearing in the sixth week. To avoid this, those "orphan" days are displayed on the top row of the calendar, so that only 5 weeks are required to display the complete month. You can see this happening for March 2019, and it occurs once or twice a year.

I set this up originally to suit a printout, and although I have adjusted the column widths as far as I can, you can see that some event titles are too large to be seen entirely. You could adjust the font size to squeeze them in, but it would not be so easy to read them. If you turn wrap text on, then it will mess up the regular display.

As already mentioned, you can have multiple copies of the Calendar sheet if you want a sheet for each month. If you do this, it would probably be better to just choose the year in one sheet (e.g. January), and then have that propagate through automatically to the other sheets using a simple formula like =January!$K$6 in K6 of all the other sheets.

Anyway, play about with it and let me know what you think.

Hope this helps.

Pete