1

I'm trying to import a column from a separate sheet and add a query to it.

Im using:

=QUERY(ImportRange("URL","SHEETNAME!c:f"),"select Col1 where Col4 contains 'statements' ")

I've been using this for years and its always ALWAYS worked. Now it suddenly doesn't work. The importing works but the query is not working and it's actually taking the data and transposing it... Any ideas why my formula isn't working?

Improve this question
1
  • A guess: query miscounted the number of header rows. Use the 3rd argument of query to provide the number of header rows (typically 1)
    – user135384
    Commented Mar 17, 2018 at 17:57

2 Answers 2

Reset to default
2

When the output of query changes in a strange way just because of different data, there are two likely reasons:

  1. Mis-identification of header rows. query tries to guess what rows contain data, and what rows are headers for that data. Solution: provide the third argument with the correct number of header rows, query(range, "select ...", 1).

  2. Mixed data types. Each data column is interpreted as being of some type: text, numbers, dates. If these are mixed together, the type is determined by majority. The other entries are either converted to majority type, or discarded altogether. The solution is to avoid mixed data.

In this case, it was the first reason.

Improve this answer
-1

Ridiculous, but after I selected one entire column and set as date, previousley it was just pulling month() and year(), the query worked back again.

Thank you very much!!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.