We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for a string to use with IMPORTRANGE that would add conditions to what is transferred.
For example:
Book 1 Applicant Form Response
A B C D E F G
1 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
2 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Fail
3 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
4 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
Book 2 Passed Applicants
A B C D E F G
1 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
2 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
3 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
I want to be able to take the informtion from this sheet (Applicant Form Response
) in book 1 A:G
and auto fill into a different book (Passed Applicants
) only if G = Pass
.
I understand the range formula:
=IMPORTRANGE("Spread_sheet_key", "Applicant Form Response!A:G")
and my Query function would be (if within the same workbook):
=Query('Applicant Form Response'!A:G, Select * where "G contains 'Pass'")
or something.
With this I am able to transfer all the data from one book to another. As stated above I only want the data of those who passed to transfer.
What other formula would be best to use and how do I nest it within the IMPORTRANGE formula?