0

I have a google form that acts as an event registration form. I would like to add the ability for multiple people to be registered on the same response submission but then have a report with only the information for one person per row instead of having multiple people in the same row as occurs on the Form Responses sheet.

I found the Forms Data Manipulation In Google Sheets question and I believe that the solution should solve my problem but I can't seem to get the formulas to work. However, I'm having immense trouble editing the formulas that are mentioned in the above question.

I copy and paste them into the formula bar and when ever I try edit anything other than cell reference in the offset function my changes and rejected and reverted back to the original formula. I might be editing the row part of the formula because I don't quite think I understand how it works.

Here is a link to a simplified version of the form's spreadsheet. I should be able to modify the actual spreadsheet if I can understand how to modify the solution to the question mentioned above to work for the simplified spreadsheet. Any help an advice you can give me so I can do that is greatly appreciated.

5
  • Please include all the relevante information in the question itself. External resources like a spreadsheet or other question could be helpful but the question should be self contained.
    – Rubén
    Jun 21, 2017 at 16:34
  • @Rubén I have tried to make the question more self contained. Please let me know if I need to add more detail or clarify anything.
    – word4q
    Jun 21, 2017 at 23:26
  • Please don't append the rephrased content. Replace the obsolete /unclear information with the improved revision. The old revision will be reachable through the revision history. You can find the original "version" as revision 1
    – Rubén
    Jun 21, 2017 at 23:29
  • @Rubén is that better now?
    – word4q
    Jun 21, 2017 at 23:38
  • I think that it's better. I just posted a refactored version of the formulas of the referred answer on a new answer.
    – Rubén
    Jun 22, 2017 at 12:15

1 Answer 1

1

The "Raw Responces" sheet of the OP has

  1. One column, timestamp. Its role is to be the "headers column"
  2. Three persons, two columns by person, name and price. Its role is to be the "data columns"

On the report sheet

  1. On M1 add the number of persons, 3.
  2. For the "header columns", on A2 add the following formula:
    =OFFSET('Raw Responces'!$A$2,ceiling((row()-row($A$1))/$M$1,1)-1,column()-column($A$1),1,COUNTA($A$1)).
  3. For the "data columns", on B2 add the following formula:
    =OFFSET('Raw Responces'!$A$2,ceiling((row()-row($A$1))/$M$1,1)-1,mod(row()-(row($A$1)+1),$M$1)*COUNTA($B$1:$C$1)+COUNTA($A$1),1,COUNTA($B$1:$C$1)).
3
  • Thanks Ruben. That works beautifully. I understand from your answer to the Forms Data Manipulation In Google Sheets question that I need to copy the header column formula for each new header column that I add. I also understand that if I change the number of 'data iterations' I need to change the number I put in M1. If I change the number of data columns per iteration do I need to change anything in the data column formula? Also, form your Forms Data Manipulation In Google Sheets answer I understand the purpose of the formula apart from the COUNTA() part. Could you explain that part please?
    – word4q
    Jun 24, 2017 at 3:45
  • @word4q: COUNTA($A$1) originally had something like COUNTA($A$1:$E$1) but as your case only has one header column when I wrote on Google Sheets COUNTA($A$1:$A$1) it was automatically replaced by COUNTA($A$1)
    – Rubén
    Jun 24, 2017 at 16:18
  • I have now added a new data column and the formula no longer works. Can you tell me what part of the formula I need to change whenever I need to add or subtract a data column please?
    – word4q
    Jun 25, 2017 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.