I currently have a QUERY function which is set up based on a start date cell and an end date cell, formula as below:
=QUERY(Haulage!$A$3:$L$29," Select * Where A >= date """&text('2022 Stats'!S1, "yyyy-mm-dd")&""" AND A <= date """&text('2022 Stats'!T1, "yyyy-mm-dd")&"""")
This is working fine but I would like to adapt it so I can also narrow the query down further with the use of a dropdown. I have the following IF formula for this:
=IF('2022 Stats'!V1="All TOCs",""," AND LOWER(K) = LOWER('"&'2022 Stats'!V1&"') " )
This seems to yield the correct results but I am struggling to get the two to work together......
Link to sheet: https://docs.google.com/spreadsheets/d/1wTWuvFwMTqJ-sjIZbXWpGOS1WKwpODj2R8KAzqlqkuw/edit?usp=sharing
CodePudding user response:
try:
=QUERY(Haulage!A3:L29,
"where A >= date '"&TEXT('2022 Stats'!S1, "yyyy-mm-dd")&"'
and A <= date '"&TEXT('2022 Stats'!T1, "yyyy-mm-dd")&"'"&
IF('2022 Stats'!V1="All TOCs",,"
and lower(K) = '"&LOWER('2022 Stats'!V1)&"'"))