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

I can get IMPORTRANGE to work like this:


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

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.

  • user 79865 answers is really helpful. Just adding in that you can use the Google Charts query language to modify the query to something different. For example: =query(importrange("URL","A:J"), "select * where (Col3 like '%Yes%')"). Reference document: support.google.com/docs/answer/3093343?hl=en. Jul 14 '20 at 20:13

Yes, you can bring the two together like so:


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