4

I'm currently using the following function to import data from one spreadsheet to another based on a value in Column K of the source spreadsheet:

=QUERY(
   IMPORTRANGE("URL","Sheet Name!A2:P1000"),
   "SELECT Col1,Col2,Col3,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 
      WHERE Col11 CONTAINS 'West'",
    1
 )

Notice I am not importing Columns 4 & 5 (D & E) because in my destination spreadsheet, those two columns need to be fixed and unchanging. However, I'm now getting:

#REF Error: Array result was not expanded because it would overwrite data in D2.

Is it possible to edit the function above and tell it to skip Columns 4 and 5 when it's pasting the data from the source spreadsheet?

  • Welcome to Web Applications. Please see tour to learn the ropes of this site. – Rubén Oct 2 '15 at 20:08
3

No, it's not possible to "skip columns".

Instead you could use two query functions, one to be used to get the columns A - C, the other to get the columns F and following.

First formula (A1):

=QUERY(
   IMPORTRANGE("URL","Sheet Name!A2:P"),
   "SELECT Col1,Col2,Col3
      WHERE Col11 CONTAINS 'West'",
    1
 )

Second formula (F1):

=QUERY(
   IMPORTRANGE("URL","Sheet Name!A2:P"),
   "SELECT Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 
      WHERE Col11 CONTAINS 'West'",
    1
 )

REMARKS:
Note that instead of Sheet Name!A2:P1000 was used Sheet Name!A2:P as this take the whole columns instead of only 1000 rows.

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.