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?

  • 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 Mar 17 '18 at 17:57
  • Bless your soul thank you! worked. – Carlos E. Rios Mar 17 '18 at 20:15
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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