A left field approach:
Use Power Query to transform your source data:
EventList:
let
Source = Excel.CurrentWorkbook(){[Name="Events7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Event", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <> null and [Date] <> ""),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {{"Events", each Text.Combine(_[Event],"#(lf)"), type text}})
in
#"Grouped Rows"
Close and load to Data Model.
Add a Measure to the data model:
All Events:=
IF (
HASONEVALUE ( EventList[Date] ),
FIRSTNONBLANK ( EventList[Events], 1 ),
BLANK()
)
Now in your "Calendar", you can use this formula (and format cells to wrap text):
Formula:
=IFERROR(CUBEVALUE("ThisWorkbookDataModel","[EventList].[Date].&["&TEXT(F6,"yyyy-mm-dd")&"T00:00:00]","[Measures].[All Events]"),"")
Use Data > Refresh All to update values after changes made to source data.
Bookmarks