Query Importrange Issue

Question

I am not sure why this functionality stopped working, but I am sure it has to do with inconsistent back end data or how the query condition "CONTAINS" needs to be changed. The IMPORTRANGE portion works just fine, but will not always pull data into the front end sheet. The query portion looks like this

SELECT Col3, Col2, Col1 WHERE Col2 CONTAINS "&'Job Number'!A1&" ORDER BY Col3 ASC,1

Column 2 contains job numbers that are xxxxx with another 3 digit code appended to end of it. It will only populate temporarily if I manually go into the sheet and edit the IMPORTRANGE range values. If I close the spreadsheet and open it again it will not populate. Does the data in Column 2 need to be a consistent datatype throughout the column or it will break the query?

Answer 1

Unfortunately we do not have a test sheet, so we can not know what the expected results would be.

Nevertheless, your formula syntax is not correct.

The correct syntax would be:

SELECT Col3, Col2, Col1 WHERE Col2 CONTAINS '"&"JOB"&A1&"' ORDER BY Col3 ASC,1

Please notice the syntax '"&"JOB"&A1&"'

(If you still have issues please share a test sheet and let us know.)