1

My knowledge is pretty basic when it comes to excel and Google Sheets. I'll explain what I'm trying to do.

I'm trying to find a way to filter information from 1 sheet to another in Google Sheets. Ideally, I would like to display an entire row if it is a certain colour. If I can't do it by colour then I'd like to display the row if a certain column within the row says COMPLETE. I have many rows to sort through so I'd really like a way to do this automatically instead of copy and paste.

Any help is greatly appreciated!

migrated from superuser.com Sep 8 '18 at 6:25

This question came from our site for computer enthusiasts and power users.

0

also, you can have this:

=FILTER('Sheet1'!A1:Z; C1:C = "COMPLETE")

  • Sheet1 - a name of the sheet from which you want to import
  • A1:Z - the range you want to import
  • C1:C = "COMPLETE" - filter out only those occurrences which have a given word in column C
0

take this:

=QUERY('Sheet1'!A1:Z; "select * where C = 'COMPLETE'"; 0)

  • Sheet1 - a name of the sheet from which you want to import
  • A1:Z - the range you want to import
  • select * - select all columns of given range
  • where C = 'COMPLETE' - where in column C are cells with word COMPLETE
  • 0 - stands for no heading rows (1 for one row, 2 for two rows, etc.)
0

if you have two spreadsheets (two Google Sheets files) you need to use:

=IMPORTRANGE("ID or URL"; "'Sheet1'!A1:Z")

  • Sheet1 - a name of the sheet from which you want to import
  • A1:Z - the range you want to import
  • "URL" - whole URL address of the sheet from which you want to import

    example: "https://docs.google.com/spreadsheets/d/10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4/edit#gid=1873076661"

  • "ID" - part of the URL address of the sheet from which you want to import

    example: "10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"


and then you can use FILTER, QUERY or both like:

=FILTER(IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!A1:Z"); 
        IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!C1:C") = 
        "COMPLETE")

=QUERY(IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!A1:Z"); 
 "select * where Col3 = 'COMPLETE'"; 0)

=FILTER(IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!A1:Z"); 
  QUERY(IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!C1:C"); 
  "select *"; 0) = "COMPLETE")

side note: Col3 - column strong text (eg. 3rd column)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.