How do I create a quarterly calendar that populates data from a table for each team?

[mtt0306]

I have 9 subordinate teams that each consist of ~5-7 cities along with directorates within our headquarters. Each team and directorate populates a shared sharepoint list of the events that they are conducting with a start date and end date. The list can be exported to either excel, CSV, or powerBI.

I would like to have the events from the exported sharepoint list populate onto a quarterly calendar where columns are dates and rows are cities grouped in their respective teams. When a city is conducting an event it would show what event on the respective dates (start date & end date).

Any help would be greatly appreciated. 

link to excel template  (mock calendar and exported data on the export data tab)

[#2]

@Riny_van_Eekelen I have read many of your replies over many conversations and hope you might be able to assist.

[#3 · Accepted solution]

@mtt0306 

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 )
  ) ) )
)

[#4]

@SnowMan55 Thank you very much. I sincerely appreciate not only the formula, but the breakdown of what you did. That makes it so much easier to understand and ensure I don't break the formula..

Cheers!

[#5]

@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?

[#6]

@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.

[#7]

@mtt0306 

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.