1

Mastersheet name: Recherche. Sheet name where the data has to be copied to: Leads. Mastersheet has data filled from Column A4 to Column M4 (both going down to 1,000 rows), looking like this:

Mastersheet

-----------------------------------------------------------------------------
#| Column A |Column B |Column C |........ |........ |........ |Column M      |
1| value    | value   | value   | value   | value   | value   | value        |
2| value    | value   | value   | value   | value   | value   | "In Overleg" |
3| value    | value   | value   | value   | value   | value   | value        |

If a cell in Column M consists of the value: In Overleg then I want the entire row (to which that specific cell belongs) to be copied to the other sheet.

Other sheet should look like:

-----------------------------------------------------------------------------
#| Column A |Column B |Column C |........ |........ |........ |Column M      |
1| value    | value   | value   | value   | value   | value   | "In Overleg" |

I have tried the following:

=filter(importrange("*my_spreadsheet-key*","Recherche!A1:A1000"),importrange("*my_spreadsheet-key*","Recherche!M4:M1000")="In Overleg")

but it errors with:

Error No matches found in the evaluation of FILTER.

How can I do this?

  • IMPORTRANGE requires the authorization of the owner or an editor of the file to access the external data. When IMPORTRANGE is nested the authorization emerging dialog is not shown. The error handling features just see that a nested function isn't returning valid values. – Rubén Nov 3 '16 at 16:48
-1

I tried the below and it worked!

=FILTER( Recherche!A4:K , Recherche!M4:M1002 = "In Overleg" )

Now each and single row where column M has the value "In Overleg" will be copied to the other sheet.

  • The above will work while the max number of rows be 1002 because the first argument is open ended and FILTER requires that both arguments be of the same row size. If you add rows below the 1002 the second argument reference will not be automatically updated and your formula will return an error. – Rubén Nov 3 '16 at 16:46
  • What will be the fix for this? K1002 as well? – Mieer Darwesh Nov 4 '16 at 9:14
  • I suggest you the other way, replace M4:M1002 by M4:M – Rubén Nov 4 '16 at 14:12
  • 1
    Please update your answer. – Rubén Nov 8 '16 at 13:46

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.