1

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

player0
  • 124,011
  • 12
  • 67
  • 124
The_Train
  • 319
  • 3
  • 11

1 Answers1

2

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)&"'"))
Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
player0
  • 124,011
  • 12
  • 67
  • 124
  • That's the one @player0 - I know this is a slightly different question but with my QUERY, I am loading it into row 2 but it is shunting my results down to row 3 onwards which is creating an additional 'blank' record which is appearing in my Stats sheet How can I stop the QUERY from dropping down in this manner? – The_Train Jun 04 '22 at 11:36
  • 1
    @The_Train change the ending `))` to `),)` – player0 Jun 04 '22 at 11:46
  • Perfect @player0 - I'm now seeking to add another dropdown option which I thought would be a simple process but I've hit a brick wall as this one consists of numbers. I will continue to try and figure it myself but may be back later for some further assistance – The_Train Jun 04 '22 at 13:15
  • Not sure if I am heading along the right track @player0 but I reached this point......=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")&"'"& IF('2022 Stats'!V1="All TOCs",," AND lower(K) = '"&LOWER('2022 Stats'!V1)&"'"& IF('2022 Stats'!W1="All Classes",," AND (L) = "&('2022 Stats'!W1)&"")),) I've also tried to add TO_TEXT as it seems to be not liking the fact that this 2nd dropdown is numerical but to no avail...... – The_Train Jun 04 '22 at 17:30
  • 1
    @The_Train hard to tell like this but 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)&"'")& IF('2022 Stats'!W1="All Classes",," and L = "&'2022 Stats'!W1*1), )` – player0 Jun 04 '22 at 17:35