The formula below is working to select All, but throws any error when I go to search specific text. ON a seperate tab, I created another query that works with the specific text.

Cell reference A4 is the drop down list with All, Status 1, and Status 2 that I would like to be able to select data from.

=Query({QUERY(importrange("xxxxx", "'"&A1&"'!A3:Q150"), "SELECT * WHERE Col6 >= "&A3&" AND Col6 <= "&B3&"", 0)}, "SELECT Col1, Col2, Col3, Col4" & IF(A4= "All",, "Where Col4 = '"&A4&"'"),0)

I have the Status 1 and Status 2 working on a seperate query, and the All working on current query, trying to make them work on one sheet. Currently only All works. When status 1 or status 2 are selected it throws this error. Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "Col4 "" at line 1, column 36. Was expecting one of: "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "" ... " " ... "-" ... "/" ... "%" ... "" ... "/" ... "%" ... " " ... "-" ...

CodePudding user response：

Try to add a space before where or after the first Col4

=Query({QUERY(importrange("xxxxx", "'"&A1&"'!A3:Q150"), 
"SELECT * WHERE Col6 >= "&A3&" AND Col6 <= "&B3&"",0)}, 
"SELECT Col1, Col2, Col3, Col4" & IF(A4= "All",, " Where Col4 = '"&A4&"'"),0)

CodePudding user response：

try:

=QUERY(QUERY(IMPORTRANGE("xxxxx", A1&"!A3:Q150"), 
 "where Col6 >= "&A3&" 
    and Col6 <= "&B3, ), 
 "select Col1,Col2,Col3,Col4"&
 IF(A4="All",," where Col4 = '"&A4&"'"), )

you can drop all of these:

• array brackets {} no need coz inner QUERY is not a range
• single quotes within IMPORTRANGE coz it applies the same rules as with INDIRECT
• zero 0 as the third QUERY argument coz it can be just omitted
• select * if you want all columns then just skip the whole select argument
• ending QUERY with &"" after B3 coz its totally redundant