Forum Discussion
Adding Event Categories to the "Family Calendar" Template
Can we add event categories to the template called, "Family Event Calendar"? The template is native to excel. If so, how?
I attached a copy of the template for reference.
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.
20 Replies
- I agree. I am trying to do the same thing. With it being labeled as a family Calendar you would think you would be able to see multiple tasks on the same day for the calendar. Not sure why excel would not have thought of that, seems logical. But Anyways, The fastest way might be to use just a Gantt chart in excel instead. That way you can put dates in and get a calendar format from that.
- Has anyone resolved this issue with several events entered for the same day, and allowing it to show up in calendar? I cannot figure out how to use the TEXTJOIN or FILTER function. Can anyone help me with a formula I can use?
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
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.
- 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
