0

I originally asked this here but my formula has now adapted and now require assistance again - Google Sheets Query using dependent dropdown

Basically, I have the following Query formula:

=QUERY(Haulage!$A$3:$U," Select * Where A >= date '"&TEXT(Stats!C1, "yyyy-mm-dd")&"' 
and A <= date  '"&TEXT(Stats!C2, "yyyy-mm-dd")&"'"&
IF(Stats!K1="All TOCs",," and lower(K) = '"&LOWER(Stats!K1)&"'")&
IF(Stats!K2="All Classes",," and (Q) = "&Stats!K2*1),)

The formula is basically to enable me to filter between a start and end date. Within the Haulage sheet is a column (C) which is primarily just numeric data but some have a letter followed by numbers. The ones with the letter at the start are not appearing in the above formula in my query. I believe to_text is a solution but struggling to get it to fit into the formula

UPDATE:

Amended formula to:

=QUERY(arrayformula(to_text(Haulage!$A$3:$U))," Select * Where Col1 >= date '"&TEXT(Stats!C1, "yyyy-mm-dd")&"' 
and Col1 <= date  '"&TEXT(Stats!C2, "yyyy-mm-dd")&"'"&
IF(Stats!K1="All TOCs",," and lower(Col11) = '"&LOWER(Stats!K1)&"'")&
IF(Stats!K2="All Classes",," and (Col17) = "&Stats!K2*1),)

Now getting the "Query completed with an empty output" error message

The_Train
  • 319
  • 3
  • 11

1 Answers1

2

try:

=ARRAYFORMULA(QUERY({Haulage!A3:A, TO_TEXT(Haulage!B3:U)},
 "where Col1 >= date '"&TEXT(Stats!C1, "yyyy-mm-dd")&"' 
    and Col1 <= date '"&TEXT(Stats!C2, "yyyy-mm-dd")&"'"&
 IF(Stats!K1="All TOCs",,   " and lower(Col11) = '"&LOWER(Stats!K1)&"'")&
 IF(Stats!K2="All Classes",," and (Col17) = '"&Stats!K2&""&"'"), ))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • This brings a formula parse error @player0 - when I adapt my initial formula with the array on the outside, I get the same Query Completed with empty output message – The_Train Jul 02 '22 at 19:36
  • https://docs.google.com/spreadsheets/d/1eiBJg0LA7x8jE8kU1ZnAMdQnJSRAvIKqzlBdtJPLmnM/edit?usp=sharing This an example with most data removed but everything else remains and the data remaining has a mix of purely numerical data and data with numbers and letters. It is set to edit for you @player0 so feel free to do whatever you like to this sheet" – The_Train Jul 02 '22 at 19:52
  • @The_Train see if the updated answer works for you – player0 Jul 02 '22 at 20:18
  • that has fixed the issue @player0 but it has now created a new one - if you look on the Data Filter sheet now you will see that the new formula has seemingly affected other formulae eg the SUMIF array in column's Z and AA which seem to have stopped working – The_Train Jul 02 '22 at 20:36
  • 1
    @The_Train see X1 – player0 Jul 02 '22 at 21:10
  • 1
    That seems to have done the trick, thanks @player0 – The_Train Jul 02 '22 at 22:28
  • One further aspect for you to look at please @player0 - I have formulae in AI4:AJ6 on the Stats sheet (highlighted in green on the sample sheet) which seem to have been impacted by the changes as well. I've tried playing around with your formula to get it to work but failed – The_Train Jul 03 '22 at 13:36
  • @The_Train multiply by 1 and enclose it with arrayformula... see your sheet – player0 Jul 03 '22 at 22:17
  • Perfect once again @player0 - thanks! – The_Train Jul 04 '22 at 07:25