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.
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.
Riny_van_Eekelen It appears you know how to change the code in this template. I used what you posted for categories. Now I need to be able to add more than one event on the same date. Currently, it only shows one event for any given date when you click to display the calendar for the month. If other events are added for the same date they do not display. I need to be able to display 'all' events for any given date. It lets me add them, but only displays the first one, not the rest.
I would greatly appreciate it if you could help me figure out how to make this happen. In summary, I need to be able to see/display multiple events on the same date in the month view.
nm_492 This particular template uses VLOOKUP to find the event of the day. And that function returns only the first one it finds in the event list for that date. If you are using Excel for MS365 or 2021, you could use FILTER and TEXTJOIN instead. So, you'd filter all events for the day and join the descriptions into the one cell you have per day. You could achieve the same by just typing multiple events in one cell in the event list. E.g. "New Years, Anne's birthday, Dinner at Tiffany's", and label it with a category "Multi" or something like that. Much easier that way and you don't have to go in and update that formulae for each day of the month in twelve sheets.
Riny_van_Eekelen thanks for the info ! Unfortunately, I will be sharing this calendar with other individuals that need to add events associated with the categories I have set up. So I think the only option is I'm gonna have to go and change the code for every day of every month otherwise this won't work automatically; any other ideas let me know otherwise I guess I'm gonna be changing some code. I guess the bright side is at least I didn't have to create all of it from scratch.
I love the calendar's main idea of having the calendar itself where people can enter events on the main calendar page and then each month will display those events for everyone in a group to see. I love the functionality if it truly would function the way most of us needed to. It's too bad somebody doesn't just create a template with all the functionality that this original one should have had. If you ever find one that exists with all the functionality I would love to know about it. I appreciate your help, thanks.
- Thanks you so much for your quick and helpful response. I'm looking forward to trying it out.
Yes I noticed the other limitations too, but for my purposes I can live with them - as long as (to your point) I don't "break" anything by adding additional category types.
Best
