Nov 15, 2021
Multi-choice grid Google Forms and separating results
I am trying to create a summary sheet (something similar to the attached image) so crew can see what they have completed. Only skippers are allowed to sign-off on tasks. The form we use now works great and summarizes the results. It can only however record one assessment at a time which is where the multi-choice grid should help.
The image below is what currently displays what we have using a pivot table, where each row is one crew member.
My issue is, when I use the multi-choice checkbox in forms the data merges into one cell when crew complete more than one task. I've looked 
at pivot tables, splitting columns etc. but just can't seem to be able to create a summary page as pictured.
at pivot tables, splitting columns etc. but just can't seem to be able to create a summary page as pictured.Any help with this would be GREATLY appreciated! Please see sample training form link below.
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Nov 15, 2021
I have placed a possible solution in the sheet called Help, which I added to your file. I made the assumption that you want the most recent designations to appear and that if someone is doing the Final, it would be the most recent activity for that skill. I also added in the conditional formatting that you showed in your question.
Is this what you are looking for?
Adam
Original Poster thenickos marked this as an answer
All Replies (10)
Hi thenickos,
This should not be a problem to fix for you. Take a look at this post about Best Practices When Asking a Question and share a copy of the linked sheet that has the permissions set for “Anyone with link can EDIT”? Thanks!
Adam
Diamond PE Docs volunteer
Silver PE Classroom volunteer
Cert. Educator 1 & 2
Cert. Trainer
(not a Google employee)
When you've received a response that answers your question, please observe these forum courtesies:
• If you had a Sheets problem, leave your demo sheet shared as View Only as part of this solution's archive,
• Click Recommend on the post that best addressed your question, and
• Post again soon!
Thank you. Would you please put an example of how you want the final results to appear in the Sheet? I'm not sure how you need the final data to be compiled. I see your example screenshot in the question but it is not clear how the individuals and skippers information would appear. Thanks!
Adam
Nov 15, 2021
Thank you for looking into this!
I've added a sheet called Sample SUMMARY. Our crew have to demonstrate competence on 3 occasions.
- Training
- PA (Preliminary Assessment)
- Final (Final Assessment)
The sample summary sheet is actually what we use now - just with surnames removed. Skippers can only select one topic/level at a time. A Pivot table works fine to merge all of the raw spreadsheet data into one but I haven't been able to achieve the same result when using the multi-checkbox in Forms.
I would expect that each cell will populate with Training,PA,Final once all assessments have been achieved. Ideally once all 3 have been completed just the text Final would show but that's not important.
Skipper data is not required to show on the sheet, but it is useful on the raw spreadsheet for our records.
I hope this makes sense!
Thank you again
Last edited Nov 15, 2021
Nov 16, 2021
I have placed a possible solution in the sheet called Help, which I added to your file. I made the assumption that you want the most recent designations to appear and that if someone is doing the Final, it would be the most recent activity for that skill. I also added in the conditional formatting that you showed in your question.
Is this what you are looking for?
Adam
Original Poster thenickos marked this as an answer
Nov 16, 2021
Now I just need to make sense of the formula and apply it to a new sheet :)
Really appreciate your help with this.
If you jump to the sheet/tab called CHAT in the file, I can walk you through it.
Adam
I placed an explanation of the formula in the CHAT tab. Let me know if you have any questions.
Adam
Nov 16, 2021
I'll give it a go and if I get stuck I'll let you know :)
My pleasure. I'm glad I could help.
Adam