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!)

  • It would be easier for people here to help you if you post a link to your sheet (or a copy of it). Use FILE > SHARE and be sure when creating the share link to choose "Anyone with the link can..." (either view or edit). I do what you're describing all the time for clients; but it would be nearly impossible to explain or suggest formulas without seeing your sheet and data. – Erik Tyler Nov 12 '18 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.