10

I want to bring in data from a separate sheet and filter it.

I can get IMPORTRANGE to work like this:

=IMPORTRANGE("URL","A:J)

But that doesn't filter. I can only get FILTER to work in the same sheet like this:

=FILTER(Sheet1!A:J, C:C="Yes")

Is there anyway to bring these two functions together and FILTER data from another spreadsheet into a new sheet, based on column C info?

The reason for doing this is to then use the auto notification setting, so whenever someone enters "yes" into the sheet I can be notified.

  • 1
    Shouldn't that be =IMPORTRANGE("URL","A:J")? (You seem to be missing a quote.) – ale Mar 27 '17 at 12:38
14

Use query instead of filter for this:

=query(importrange("URL","A:J"), "select * where Col3 = 'Yes'")

The command query can refer to the columns of the array that it receives in an abstract way: Col1, Col2, and so forth, without being tied to their placement within the sheet. So, it filters the thing "in the cloud" and then puts the result into the sheet.

In contrast, filter can only refer to columns by the cell names like C:C. This means that the array must first be placed into the sheet, and then be filtered in place according to its own values. This runs into circular dependency.

3

Yes, you can bring the two together like so:

=filter(IMPORTRANGE("URL","A:J"),INDEX(IMPORTRANGE("URL","A:J"),0,3)="Yes")

protected by Community Feb 1 '18 at 22:44

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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