How to add an argument and a dynamic range to a google sheets?

Question

I'm creating a basic dashboard for work and I can't seem to figure out how to create a query that changes depending on the date that has been selected as a user.

Due to the nature of the business I am not able to share the project file. However I have created a basic one just so you have the gist of what I'm trying to achieve.

Essentially what I'm trying to do is to have the adjust the values depending on the date that was chosen by the user.

https://docs.google.com/spreadsheets/d/1VRUtTfrJIL2mIOKem-OHaITDjj-lRjGFQbTvnMwX-nU/edit?usp=sharing

So based on this spreadsheet, when having the date set to 05/07/2022 I would like the table to show only information where expected date is 05/07/2022 and the state is Expected. Same for the opposite on the late side.

Whereas where I would change the date to 06/07/2022 it would only show the names with expected date of 06/07/2022.

I hope that makes sense, I am unsure on how else to explain it.

Does anyone have an idea on how to approach it?

Answer 1

try:

=QUERY('Sheet 2 basic data'!B4:F11, 
 "select B,C,D,E
  where F = 'Expected'
    and todate(D) = date '"&TEXT(C4, "e-m-d")&"'
  limit 15", )