Forum Discussion
Adding Event Categories to the "Family Calendar" Template
EricHammons Yes, you can. To expand the list of categories is easy. Change the named range "Event_Categories", currently pointing to the list 5 categories in H5:H20. So, if you want to work with 10 categories, set this named range to H5:H25, using the Name Manager on the Formulas ribbon and copy the format of cell H20 down to H25 (to make it look good). Now you can choose from these 10 categories in column E on the first sheet.
But, this particular template comes with one big flaw. The monthly sheets are linked to the event list by VLOOKUP. This function can only pick-up the first event for any given day. You can enter multiple events for the same day, but the calendar will only show the first one from the list. That renders the template completely useless, in my opinion. You'd need to rewrite this formula and use modern functions like TEXTJOIN and FILTER to overcome this serious lack of functionality.
And then there are a great number of conditional formatting rules that set the color of each day number in the monthly sheets based on the type of the first (!) event listed that day. So, you'll have pick some colors first and add rules for each of the added categories, on each of the monthly sheets.
All of this support my own opinion that these standard templates are not meant to be tinkered with. Use them as they are or not use them at all.
Thank you for any info you can share.
lhaven29 I lost track of this thread and missed all of the responses since my last. I adapted the template that was attached to the first posting and for the month of January, it now has the TEXTJOIN and FILTER formulas in place. Feel free to do the same for the other months.
Riny_van_Eekelen Hi thank you for all your help. I am new to excel, I would like to use the following template. However, I am unable to get the colors to change with the category. I have added more categories and renamed them. But when I pick the name from the drop down list it will not change the color to the color I have chosen. If you can assist with this, I would gladly appreciated. Thanks
Vivia1320 Thanks for sending the file. It's the one I attached earlier but you didn't include the added categories. Anyway, this modified template allows for returning more than one event per day. With regard to adding categories, you can indeed do that, but then you need to add a conditional formatting rule for each new category to each month's sheet, similar to the existing ones (see picture below)
But be aware that when you enter two events on the same day you can NOT use two different categories. You could have a category called "Multiple" or so.
