Forum Discussion

mtt0306's avatar
mtt0306
Copper Contributor
May 08, 2024

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

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)

excel
Formulas and Functions
Power BI
  • SnowMan55's avatar
    SnowMan55
    May 10, 2024

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

     

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    May 10, 2024

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

     

    • mtt0306's avatar
      mtt0306
      Copper Contributor
      May 13, 2024
      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!
    • mtt0306's avatar
      mtt0306
      Copper Contributor
      May 13, 2024

      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?

      • mtt0306's avatar
        mtt0306
        Copper Contributor
        May 13, 2024

        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.

Resources