How do I add a list of events onto a seperate tab to a monthly calender tab?

Occasional Visitor

Hi all, 

 

So I have a list of events for the year and I would like to capture specific ones marked with a Y onto a new tab that contains a month view calendar.   I would just like the name of the event to appear under the correct date on the calendar. I would also like the days to populate larger if need be to capture a range of events on one day in a list view.  I hope this all makes sense? 

 

I have attached the excel sheet for clarity. 

 

How do I do this? Help would be much appreciated. 

 

Thank you

 

 

2 Replies

@FIFI115 So first of all I would suggest using Start date and End date on the year calendar to make filtering much easier.  i would also suggest formatting that table as a table to both make it easier to view, filter, and use.  Then a simple formula on your monthly calendar could be:

=FILTER(Table1[Fesival],(Table1[Start]<=E10)*(Table1[End]>=E10),"")

EXCEPT that your monthly calendar uses merged cells everywhere causing problems.  I see no reason you couldn't unmerge all the columns until you get to the region where you have the mini prev and next months.  Not sure what to do there except using monospaced font and creating a string version that would be in a single cell or at least a single column.

@mtarler and as for making the days automatically expand for additional space when needed, that would get rather complex. i would suggest modifying the above simple answer as follows to show the first X number and then "more ..." if needed

=LET(events,FILTER(Table1[Fesival],(Table1[Start]<=I22)*(Table1[End]>=I22),""),
IF(ROWS(events)>5,CHOOSE({1;1;1;1;2},INDEX(events,SEQUENCE(4)),"more …"),events))

In the attached version I modified dates to make them overlap and unmerged some dates to show how the original formula works for <=5 dates, spill error for >5, and then using the above formula show 4+"more..."