The other day I received just that, a challenge.
Basically, our Kindergarten program is half-day; the teachers have an AM & PM class. They wanted a Form to collect information but depending on a selected answer, they wanted the information to go into one sheet versus another.
At first, I thought this was simple. Input an “IF” function to designate whether the information in a particular row should go to an AM or PM sheet. The problem with this course of action, data which doesn’t meet the “IF” condition would leave a row blank. Example for an AM sheet:
|Timestamp||AM or PM?||Name:||Score:||Notes:|
|05/26/16 08:13||AM||Tommy||21||Vowel-Consonant-Vowel trouble|
|05/27/16 09:12||AM||Timmy||14||Decoding issues|
So I had to search and search and search. I went to a variety of Google Forms forums, YouTube tutorials, and the Google spreadsheets function list. After a little while and no definitive answer, I started to try things out on my own. Eventually, I discovered a solution!
=QUERY(IMPORTRANGE(“Google sheet ID“,”Google tab name!column letter:column letter“),”Select * Where Col# = ‘text‘”)
Below are some resources (Tutorial video, embedded Form, and Sheet)