Forum Discussion
How do I create a quarterly calendar that populates data from a table for each team?
See the attached workbook for a partial solution. See the notes and references to documentation on the _Info worksheet.
This solution requires Excel 365 or Excel for the web.For reference, here is the primary formula:
=LET( rows_thru_marker, MATCH("End of Cities/Dir.", $A:$A, 0), main_range, OFFSET(B9, 0, 0, rows_thru_marker - 9, 111), MAP( main_range, LAMBDA(cell, LET( city, OFFSET(cell, 0, 1 - COLUMN(cell)), date, OFFSET(cell, 5 - ROW(cell), 0), events_on_date, FILTER( 'Export Data'!$E$2:$E$26, ('Export Data'!$C$2:$C$26 =city) * ('Export Data'!$G$2:$G$26 <= date) * ('Export Data'!$H$2:$H$26 >= date), ""), display_events, XLOOKUP(events_on_date, tblEvents[Event], tblEvents[Display], "", 0), TEXTJOIN( "; ", TRUE, display_events ) ) ) ) )
See the attached workbook for a partial solution. See the notes and references to documentation on the _Info worksheet.
This solution requires Excel 365 or Excel for the web.
For reference, here is the primary formula:
=LET( rows_thru_marker, MATCH("End of Cities/Dir.", $A:$A, 0),
main_range, OFFSET(B9, 0, 0, rows_thru_marker - 9, 111),
MAP( main_range, LAMBDA(cell, LET(
city, OFFSET(cell, 0, 1 - COLUMN(cell)),
date, OFFSET(cell, 5 - ROW(cell), 0),
events_on_date, FILTER( 'Export Data'!$E$2:$E$26, ('Export Data'!$C$2:$C$26 =city)
* ('Export Data'!$G$2:$G$26 <= date) * ('Export Data'!$H$2:$H$26 >= date), ""),
display_events, XLOOKUP(events_on_date, tblEvents[Event], tblEvents[Display], "", 0),
TEXTJOIN( "; ", TRUE, display_events )
) ) )
)
SnowMan55 following up to some of your questions.
1. For Team level and HQ level events, I would like them displayed similar to the cities, in that if that team or directorate is conducting an event, it would be listed on their row corresponding to the date.
2. For events that last several days, is it possible to have the event listed only once, but stretch between the start date and end date?
SnowMan55 disregard #1. I created an "if" formula in the export data tab that if the city is blank, replace value from Team column and put the Team in the City Column. It works just fine in the formula you created. Same with HQ events
So really the only question is #2, instead of an event repeating for each day, it's only listed once but bookended on the start and end dates.
See the attached workbook for two possibilities. But if you are thinking that displaying the text for a multi-day event only once will allow you to use a longer display value without widening the cells…it won't. By design, every cell in the main range gets a calculated value. You can't merge cells that both have content (even if part of that content is an empty string).
VBA (or possibly Office Script) could be used instead of (or in addition to) the main formula, as it can conditionally remove values, merge cells, change fill colors, etc. And it could encode whatever rules are needed to handle ambiguities that might be caused by overlapping events in a city. But in addition to raising security concerns, using VBA is a level of effort (coding plus back-and-forth on changes) that I am not going to engage in.
But if you feel a particular visualization is important, you always have the option to make a copy of any generated worksheet, then copy the main cells within the new sheet, and Paste Values back into them; then you can make whatever visual changes you want.