Google sheets - Is there a formula to automatically reformat select data from another sheet?

Question

I have a Google Sheet that is capturing Google Form responses with, of course, each response recorded on its own row. Quick blurb on the form itself: it's capturing a daily log for a construction project that can have up to 6 subcontractors working on it on any given day.
Assuming it would be impossible to format the data as it comes in on the Form Responses tab, I created a second tab in the sheet to pull data from the Form Responses tab, thus allowing me to adjust the format.
Ideally, I want the data to fit in the "viewable window" so that the user doesn't have to scroll all the way over every time, and so I want to put the 'Sub' responses into a mini-grid format. The sheet currently looks like this:

I want the form to auto populate the sheet to look like this (the timestamp of the form response being the unique identifier):

Is there a formula that can essentially query the data from the Form Responses tab and transpose a section of it? Many many thanks in advance for any help.

Hard to give specific help without an sample copy, but have you looked at the QUERY() function? — Calculuswhiz

marikamitsos marikamitsos · Accepted Answer · 2020-09-16T03:57:29

Similar questions have been answered before.

Still. Here is something to work on:

=query({'Form Responses'!A2:H;'Form Responses'!A2:D,'Form Responses'!I2:L;'Form Responses'!A2:D,'Form Responses'!M2:P; 
    'Form Responses'!A2:D,'Form Responses'!Q2:T}, 
          "select * where Not Col5='' order by Col1 label Col1 'Timestamp', Col5 'Sub"&char(10)&"Name' ")

(Please fill in the rest of your labels and adjust ranges to your needs)

Google sheets - Is there a formula to automatically reformat select data from another sheet?

1 Answers