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.
Best Answer
Use
query
instead offilter
for this: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 likeC: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.