Need questions related to initial questions? [closed]

Question

I'm trying to make a google form that my friends and I can use to enter our board game scores into an excel sheet, which then calculates our overall and per game scores... Does anyone know how/if I can achieve the following on a form:

Q1: Who is playing? (Check box selection)

Q2: Which Game? (Drop down menu)

Q3: Score? (with multiple lines needing answers based on who was chosen as playing in Q1)?

Me  (space to enter score)
Him (space to enter score)
Her (space to enter score)

(With each of these going to a column for that person in the spreadsheet)

Anyone know if this is remotely possible?

Answer 1

If you have a short list of players you could change the question to a drop down of permutations. after that:

To the lower right of a multiple choice or dropdown question is a vertical ... menu (next to the required slider) "Select go to section based on answer" from there you can pick a section of the form to go to next based on that answer being selected from a menu that will appear beside each answer. You would need to do that for each permutation in Q1. There will be a section like the Q3 you describe based on each permutation. https://support.google.com/docs/answer/141062?hl=en

Each section will have its own set of columns this will mean multiple columns of responses for each player however only one per submission will be filled out.

Consolidating the player scores back to a single column in the sheet could be done with a formula similar to this: Concatenating only filled cells

Answer 2

spreadsheet demo: https://docs.google.com/spreadsheets/d/

where column M consists games which can be selected in the drop-down menu of column D

then cell E3 contain this formula which detects which checkbox was marked:

=ARRAYFORMULA(IF(A3:A=TRUE; $A$2&":";
              IF(B3:B=TRUE; $B$2&":";
              IF(C3:C=TRUE; $C$2&":"; ))))

in column F you can enter score as requested and that score is then distributed for each player as requested

then SUMMARY is calculated based on 4 logic formulas in J3, J4, J5 and K3 which covers for every possible state:

cell J3:

=IF(AND(SUM(G3:G)>=SUM(H3:H); SUM(G3:G)>=SUM(I3:I)); G2&":";
 IF(AND(SUM(H3:H)> SUM(G3:G); SUM(H3:H)> SUM(I3:I)); H2&":";
 IF(AND(SUM(I3:I)> SUM(H3:H); SUM(I3:I)> SUM(G3:G)); I2&":";
 IF(SUM(H3:H)=SUM(I3:I); H2&":"; ))))

cell J4:

=IF(AND(OR(J3=H2&":"; J3=G2&":"); OR(J5=G2&":"; J5=H2&":")); I2&":"; 
 IF(AND(OR(J3=I2&":"; J3=G2&":"); OR(J5=G2&":"; J5=I2&":")); H2&":"; 
 IF(AND(OR(J3=I2&":"; J3=H2&":"); OR(J5=H2&":"; J5=I2&":")); G2&":"; )))

cell J5:

=IF(AND(SUM(G3:G)<=SUM(H3:H); SUM(G3:G)<=SUM(I3:I);  G2&":"<>G2&":"); G2&":";
 IF(AND(SUM(H3:H)< SUM(G3:G); SUM(H3:H)< SUM(I3:I)); H2&":";
 IF(AND(SUM(I3:I)< SUM(H3:H); SUM(I3:I)< SUM(G3:G)); I2&":"; I2&":")))

cell K3:

=SORT({SUM(FILTER($F$3:$F; REGEXMATCH($E$3:$E; G2)))&" points";
       SUM(FILTER($F$3:$F; REGEXMATCH($E$3:$E; H2)))&" points";
       SUM(FILTER($F$3:$F; REGEXMATCH($E$3:$E; I2)))&" points"}; 1; 0)

of course cells A2, B2 and C2 can be rewritten to match the actual names of players