3

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'.

I'm using the following formula:

=query(ImportRange("0AgbNxxZ5QnpGdEU2R3BLVDlhd0t6VlhCbS1kQWNYT2c","Renewals!B:C"),"select Col2, Col3, Col5 where Col10='Yes' ")

(which I've hashed together having looked on forums & YouTube!)

But it keeps coming back with errors: Col10 does not exist, etc.

How can I fix it?

2 Answers 2

3
+100

There are two things that you need to take into consideration.

Consideration

  1. The IMPORTRANGE function requires two parameters (strings):
    • spreadsheet key
    • data range
      The data range needs to correspond to the number of columns you're about to use. In your case, you selected only column B and column C. Therefore, going beyond col2 will end up in failure.
  2. There are two distinct ways to use a parameter:
    • numbers
    • strings

Data

enter image description here

Number

=QUERY(                                                // data
  IMPORTRANGE(
    "0AluAYY6ZHeWYdE5XampzQVl4dVpxZ3FoVW9mUkdwQkE",    // spreadsheet key 
    "DATA!A:C"                                         // datarange
  ), 
  "SELECT Col1 WHERE Col3=" & C2, 0)                   // query
)

enter image description here

String

=QUERY(                                                // data
  IMPORTRANGE(
    "0AluAYY6ZHeWYdE5XampzQVl4dVpxZ3FoVW9mUkdwQkE",    // spreadsheet key 
    "DATA!A:C"                                         // datarange
  ), 
  "SELECT Col1 WHERE Col2=" & "'" & B2 & "'"           // query
)

enter image description here

Example

I've created an example file for you:

1

Short answer

Replace Renewals!B:C by Renewals!A:J

Explanation

The problem is that the second parameter of the QUERY() function is doing references to column 10 while the columns specified by the range in the IMPORTRANGE() function only include two.

The solution could be
1. To change the range parameter of the IMPORTRANGE() to include enough columns.
2. To change the select parameter of the QUERY() function to make reference only to the available columns.

As the question said that the is required that the result only include rows where column J values are Yes, then the solution is 1.

Your Answer

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

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