Forum Discussion
Create autopoulating excel calendar with colour coded categories
Hi everyone,
I have a long list of events in an excel table format that I would like to turn into an autopopulating dynamic monthly calendar which is colour coded.
The problem I am facing is the colour coding...
I need to be able colour code the dynamic calendar based on the category type selected. Can this be done? Conditional formatting is not working and I do not know VBA.
Thank you
You'd have to insert enough rows under each date row to accommodate the maximum number of events per day that you expect. Let's say that you won't have more than 8 events per day. You currently have 1 row for each day, so you'd have to insert 7 new rows under each day row.
You can then omit TEXTJOIN from the formulas:
=IFERROR(TEXTJOIN(REPT(CHAR(10),1),TRUE,FILTER(Schedule[[Title]:[Title]],(Schedule[[End Date]:[End Date]]=(DATEVALUE(B6&"-"&MoMonth&"-"&MoYear))),"-")),"")
becomes
=FILTER(Schedule[[Title]:[Title]],Schedule[[End Date]:[End Date]]=(DATEVALUE(B6&"-"&MoMonth&"-"&MoYear)),"-")
This formula will automatically spill to as many cells as needed.
Because there is now only one category per cell, you can apply conditional formatting rules - one rule for each category.
7 Replies
Hi! I'm trying to accomplish something similar, and your solution seems really helpful. Would you be open to sharing your file?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Thanks for looking into this - I’m stuck. No idea how to add category colour coding!
https://www.dropbox.com/scl/fi/3stl3pl7pnt4v4ziht8lj/Planner-template.xlsx?rlkey=kedandd3f12nmxp97pl5f0cdi&dl=0
Thanks. What you want is not possible - the Month View - All categories and Yearly View - All categories sheets contain formulas that combine events from multiple categories in a single cell.
Excel does not support colouring part of the result of a formula, so we cannot colour - say - a Category 1 event red and a Category 2 event green.
