1

Hello Google Masterminds,

I come to you since I have followed almost every single youtube video and every single thread here and I been working with this freaking form for the past 3 weeks and have had 0 progress.

Everytime when I use all the formulas as create by you smart people I get errors and yes I adjusted them to my set data.

  • Row A & B are the header information,
  • Row C-H 1st kid
  • Row I-N 2nd Kid
  • Row O-T 3rd kid
  • Row U-Z 4th kid
  • Row AA-AF 5th Kid
  • Row AG-AL 6th kid

Childern info has to go to Deelnemers Lijst Tab

  • Row AM-AQ 1st Volunteer (vrijwilliger in Dutch for volunteer)
  • Row AR-AV 2nd Volunteer
  • Row AW-BB 3rd Volunteer
  • Row BC-BF 4th Volunteer
  • Row BG-BK 5th Volunteer
  • Row BL-BP 6th Volunteer

Volunteers info has to go to Vrijwilligers Lijst Tab

Kids 1 to 4 are mandatory 5 and 6 are optional and sometimes not filled in. Volunteer 1 is mandatory and 2 till 6 are optional. (edit: No clue if the formula does not work if it doesn't see information to copy in set location.)

I do not know what has been going wrong and I kind of gave up but I am in really dire need this needs to get digital cause doing it all by hand it taking way to much time we could use else where as an non profit organisation. We do this to let the kids have some fun in their last week of Summer Holiday. Can someone please help me out here, I am just stuck and non of the video's nor formula's seem to work. For some reason the moment I put a , as shown in the other formulas my entire formula stops working.

Improve this question
3
  • @doubleunary Thank you for your response. If I understand you correctly this is what I was trying to reproduce: support.google.com/docs/thread/17484596/… So that 1 full like of google form sections turn into the individual sign ups underneath eachother. I tried several youtube videos to understand the coding, and I tried to adjust the formula accordingly to the link I provided in this comment. None seemed to work and it kept saying error, or invalid and so on.
    – Luuk Timmerdorp
    Commented Jul 2, 2021 at 17:16
  • Part 2: I dont need someone to make me the file and to work it. but if someone could explain it to me how to do it I can figure it out myself, but at this point in time everyone tells you to do this or that, but does not tell you WHY you do set word or command and what it does for you in the listing. SO I have no clue what I am editing to where
    – Luuk Timmerdorp
    Commented Jul 2, 2021 at 17:17
  • 1
    To best get help, please add say 10 rows of realistic-looking sample data in Form Responses, and show your hand-entered expected results in the sample spreadsheet. Clearly identify where the expected results are, and explain why they are the correct results.
    – doubleunary
    Commented Jul 2, 2021 at 18:57

1 Answer 1

Reset to default
1

You have a Google Form that contains information regarding kids (at least 4, 6 max) and volunteers (at least 2, 6 max)

You want to show the Form Responses on two sheets: one for Kids (Deelnemers Lijst) and one for Volunteers (Vrijwilligers Lijst).

Kids

Enter this formula in cell A1 of "Deelnemers Lijst"

={Query({'Formulierreacties 1'!A1:B,'Formulierreacties 1'!C1:H},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!I2:N},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!O2:T},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!U2:Z},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!AA2:AF},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!AG2:AL},"select * where Col3 is not null")}

Volunteers

Enter this formula in Cell A1 of "Vrijwilligers Lijst"

={Query({'Formulierreacties 1'!A1:B,'Formulierreacties 1'!AM1:AQ},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!AR2:AV},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!AW2:BA},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!BB2:BF},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!BG2:BK},"select * where Col3 is not null");Query({'Formulierreacties 1'!A2:B,'Formulierreacties 1'!BL2:BP},"select * where Col3 is not null")}

Comments:

  • In both cases six queries are combined/"stacked" in an array. The First query is for the 1st Kid/volunteer; the send query is the the 2nd kid/volunteer, and so on.
  • Since some information is mandatory, each query has a where clause = where Col3 is not null. Regardless of whether a query relates to kids or volunteers, Col3 is the kid/volunteer name - if this field is null, then the that group is ignored.
  • the where clause is included in every query; that is not strictly necessary but it makes editing the formula easier since each query has an identical structure.
  • the first query in each group references row#1; other queries reference row#2 - this ensures that the header row is included in the output.

Kids - sample data

Kids

Kids - Output

Kidsoutput

Volunteers - sample data

volunteerdata

Volunteers - output

volunteeroutput

Improve this answer
5
  • O mate, your an absolute legend. This is more than I asked for! I simply wanted to understand the issue of the formula and you went beyond it and just made the formula. I am going to see if I can implement it and make it work as you shown here. The comma's seem to not work for me on the first entry when it is attached to the first entry / location. But I think I can figure that one out.
    – Luuk Timmerdorp
    Commented Jul 3, 2021 at 20:50
  • All I can say is THANK YOU so much for your time. I really can't express your efforts here and how much it means to me you went beyond explaining and just did it.
    – Luuk Timmerdorp
    Commented Jul 3, 2021 at 20:51
  • Your spreadsheet locale reflects whether the spreadsheet uses a decimal point or decimal comma. Depending on this setting in your spreadsheet, a separator might be a comma instead of a semi-colon (or vice verse).
    – Tedinoz
    Commented Jul 5, 2021 at 0:36
  • I could see right off the bat what you were trying to do. There are many precedents for this kind of thing, but sometimes a particular situation is slightly different and it’s easier (and faster) to develop the actual answer than try to explain “how” to develop the answer. I’m glad this has been useful; if you wish, you could accept the answer.
    – Tedinoz
    Commented Jul 5, 2021 at 0:40
  • This is also very easy to add or remove rows from set query, since it is seperate for every group.
    – Luuk Timmerdorp
    Commented Jul 5, 2021 at 23:55

Your Answer

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

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