CODEWITHSUNDEEP
google-sheets
Chris Rosendin
Chris Rosendin

Reputation: 367

Dynamic calendar in Google Sheets - how to query in items from a list

I'm building a calendar in sheets where you can change the heading (say from 1/1/2021 to 2/1/2021) and all the events for that month auto-populate from a list in another sheet. I just can't get it to work! The ideal would be stacked items like this: (all are in the same day)

The query that DOESN'T work is here:

=QUERY( { Events!A:A&" - "& Events!C:C&" - "& Events!E:E, Events!I:I }, "select Col1 where Col2 = date '"&text(($A$1+C4-1),"yyyy-mm-dd")&"'" )

This fails with "Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1. Actual: 984." My intent is to concatenate columns A, C, and E to Col1 and I to Col2 (date column)

Here is a sample sheet The ideally formatted solution is in green, the broken formula is in pink.

Anyone have a solution for this? Appreciate the help!

Adding in the final solution courtesy of Player0:

=IFERROR(CONCATENATE(ARRAYFORMULA(QUERY({Events!A:A&" - "&Events!C:C&" - "&Events!E:E&char(10), Events!I:I}, "select Col1 where Col2 = date '"&TEXT(($A$1+B4-1), "yyyy-mm-dd")&"'",0 ))),"")

Added a char(10) to create a line break.

Upvotes: 1

Views: 576

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY({Events!A:A&" - "&Events!C:C&" - "&Events!E:E, Events!I:I}, 
 "select Col1 where Col2 = date '"&TEXT(($A$1+C4-1), "yyyy-mm-dd")&"'" ))

see:

https://webapps.stackexchange.com/a/131436/186471

Upvotes: 2

Related Questions