'In Google Sheets how do I query another sheet for a column matching a particular date?
I have two sheets named 'MAR 2022' and 'Roster'. I am wanting to query the Roster sheet and display the header and value that matches a date value in another cell in the MAR 2022 sheet.
QUERY('Roster'!,"select A, B where A = DATE '"&TEXT('MAR 2022'!$A$2,"yyy-MM-dd")&"' ",1)
In the Roster sheet, I have a Date column and headers for activities "Laundry", "Clean Floors", with the name of person beneath. Each date has its own row. In the MAR 2022 I want to basically be a calendar that shows for each day and who is responsible for the activity.
[Roster Sheet] https://i.stack.imgur.com/KNsfH.png
[MAR 2022 Sheet] https://i.stack.imgur.com/1TQun.png
Any ideas what I'm doing wrong?
Solution 1:[1]
Hmmm, there are likely other ways to solve this but using query I would suggest changing how the source table is laid out since query does not provide for concatenation of 2 columns (Ex. you want to show the duty and person's name combined, Laundry: John)
I was able to accomplish by changing your roster source table as follows:
The March tab looks like the following:
The formula for the days of the week is:
=text(A2, "ddd")
The date functions in A2 is:
=transpose(Roster!A2:A)
The QUERY() functions in A2 thru E2:
=query(Roster!$F$1:$H, "SELECT G WHERE F = DATE '"&TEXT(A2,"yyyy-mm-dd")&"' LABEL G ''", 1)
Solution 2:[2]
try with row#2 as date formatted dd
=query((arrayformula(split(flatten(Roster!$B$1:$D$1&": "&Roster!$B$2:$D&"~"&Roster!$A$2:$A),"~"))),"select Col1 where Col2="&A2&" ")
and drag to the right
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | JohnA |
| Solution 2 |