Using QUERY to pull data from an entire row...

Question

I started a new project, copied it, tested it (it failed) and am now posting the copy for examination. The QUERY syntax in column B is acceptable. I've noticed the cell references in the column B formulas increment after running the form submit. If anyone can spot the problem, they get a big hoorah and my gratitude. The Form file and the Responses Sheet are linked below:
Form: https://forms.gle/pngDFmnGGWzqNqEY6
Sheet: https://docs.google.com/spreadsheets/d/1WxviBJ8oJhAd9n_5EbZldkyLNyBh0FVlcsXuQfA_nkw/edit?usp=sharing
The permissions have been adjusted to allow for editing...
I submitted three responses which only managed to increment the reference values. I'm going to  attempt James "mreighties" and Ben Liebrand's solution on a copy. Will return around 43 o'clock...

Ben Liebrand · Accepted Answer

Hi, Burt Paulie,
 
Your example spreadsheet is in View Only mode so I am not able to do anything.
 
You will need to test this yourself.

What you need to do first is clear everything in the 'City 1' tab  (IMPORTANT)
 
Then enter the following formula into cell A1 only (Do not drag it down as an error will occur)
 
=query({'Form Responses 1'!A1:F},"select * where Col1 is not null and Col2 = 'City 1' ",1)
 
The above formula will automatically populate the rows when more 'City 1' submissions are recieved.
 
I hope this helps.
 
Ben Liebrand

James/mreighties · Accepted Answer

Hello,
 
On the "City 1" tab select the columns A:F and press the "Delete" key. Then in A1 use this formula...
 
=QUERY('Form Responses 1'!A:F,"Select * where B = 'City 1' ",1)
 
...this will pull all responses that have "City 1" in column B plus pull the headers as well.
 
Do the same for "City 2" and City 3" tabs and copy/paste the formula to the other "City" tabs and change "City 1" in the formula to either "City 2" for the "City 2" tab and "City 3" for the "City 3" tab.
 
Of course it won't pull anything except the headers until the "Response" sheet has some responses in it.

James :)