Filter records from one Google Sheet to another

I have a list of contacts in a Google Spreadsheet with a column identifying who a client is in sheet-1. I wanted to create a new spreadsheet called sheet-2 and pull all my contacts from sheet-1 to sheet-2 if they where clients.

To do this there are several techniques involved: filter, importRange and search.

Showing results from one sheet to another

The first challenge was showing results from one sheet to another.

This formula gets all the records from sheet-name in record A1 to A200000. We have to set a range here but it can be well over the amount of records in the sheet:


Your sheet-key can be found in the URL of the sheet, it is a long string at the end of the URL made up of numbers, letter and dashes:


Search the results

Next we need to filter the results using the SEARCH function. We search for all results in a specified column that match our term: Yes


This gets results from one sheet to another and searches for the value Yes and displays these results.

Show all columns for the searched results

Now show more columns of the searched results from sheet 1


All the above functions are well documented be Google with much more detail and explanation: https://support.google.com/docs/topic/1361472?hl=en&ref_topic=2811806

Source: https://productforums.google.com/d/msg/docs/Pa6obI9uLHs/SHJOB9mXBjsJ

Initial source for getting recodes from one sheet to another: https://www.youtube.com/watch?v=Yhy7555OiJ4


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s