I have a sheet 'Form responses 1' that is autofilled from a form. I was told to carry out all calculations on a seperate sheet to avoid any issues.
So in a seperate sheet I am pulling in the date, time started, time finished, and orders fields using, this shows the fields I need for each row added.
=QUERY('Form responses 1'!B2:E,"")
I have added some sums/calculations into the next few columns, but they dont auto populate when a new row is added by the form. So I have tried just doing the calculations and pulling the live data but it never works.
For example this combines tries to work out a duration between time started and finished (CD)
=Query(sum('Form responses 1'!C2:C-'Form responses 1'!D2:D)*24)
But it only shows one row response instead of copying down. The following works but adds rows well below the 10 so far inputted, so I have 0:00 in every row.
=ArrayFormula(('Form responses 1'!D2:D - 'Form responses 1'!C2:C))
I think im on the right tracks with this, but how do I get it only show responses for the rows that have data?
I have also tried to concatanete two cells, date and time, so that I can add them to Data Studio, however my query does work but instead of filling the column it fills one cell with all the responses.
=ARRAYFORMULA(concatenate(text('Form responses 1'!B2:B,"dd/mm/yyyy")&" "&text('Form responses 1'!C2:C,"hh:mm:ss")))
Im just really struggling with how to pull data from another sheet and merge, it but also to get it to work when new responses are added (the fact im working with date.time data is probably not helping either!)