0

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!

3 Answers 3

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)