Events Month per Sheet Calendar help

Hi,

I've probably gone about this in a very convoluted way to make a 'pretty' calendar. But essentially it's an events calendar that could ideally be updated via the data worksheet and the dates would then fill in the corresponding months sheet under the correct date/s (homepage sheet is purely for navigation). Some events may range over multiple days and possibly into another month. So far i have just filled it in manually but would love to try and get this to work with a bit of excel magic. (More events will likely be added in the future)

I believe conditional formatting might help with my colour coding, so also just want to check that i can then 'colour code' the correct type of event once its on the calendar. If it's easier for the sample i believe November may be the best example month or just insert a test event if helpful

My calendar is all set up manually ( i know i did it the longest way ) and does already have bank holidays and school holidays blocked in, but i can move these around if i need to. On average there'll probably be at most 5 overlapping events at any one time per date (very rare)


Example attached (highlighted green section of the table is a separate set of dates - these can be ignored it's probably easier for me to just to input these separately for the time being as set up/de-rig)

Hello, thank you for signposting me to this, definitely starting to get my head around yours and its very helpful and suits my needs thanks! I'm starting to get my head around the correct wording for conditional formatting and formulas so i was just wondering with using your calendar as an example whether there's a way to then colour code the 'type/occasion' to the 'event/name'. for example if i made a drop down list and the type was selected as birthday for jane doe could that then block out the colour to blue in the calendar and still have 'jane does' name showing there.

Thank you I really appreciate it, I think I'm starting to understand how the formula works too which is a bonus

I may as well ask, if I added in an end date column for dates that went across multiple days. Would it be possible for that to translate into the calendar too? so if an event ran from 5th jan to 8th jan, could that transfer across the multiple days and also be shown in the calendar?

Some of my calendar files have the facility to accept date ranges. Essentially, the Event gets expanded in a second table so that there is a listing for each day within the date range. I'll attach one later on (just about to take a break now), so that you can see it in operation.

Pete

I've searched through the archives to come up with a few links to other threads which you might find to be helpful. My files generally have one sheet to record details of the "event", and then another sheet to display a monthly calendar. The month and year can be selected by drop-downs, so there is no real need to have a sheet for each month, although that could be accomplished quite easily by copying the calendar sheet and "fixing" the month.

This first link:

https://www.excelforum.com/excel-gen...-calendar.html

is for a Leave Calendar which accommodates date ranges for the type of leave booked. Up to 10 "events" can be shown for each day. This next link:

https://www.excelforum.com/excel-for...worksheet.html

has up to 5 events per day spread over 2 lines, and with an ability to select more events in groups of 5. There are also links in that thread to other calendar files.

This third link:

https://www.excelforum.com/excel-gen...ormatting.html

has calendars from other contributors as well as me, but mine shows conditional formatting for each different event for each day.

This final link includes a calendar file which uses date ranges:

https://www.excelforum.com/excel-new...-calendar.html

as well as having an ability to select the group from which to display the events.

You will see in the calendars that take date ranges that these records are expanded automatically into a second table, where there is one record for each day of the range.

Hope this helps.

Pete