1

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?

  • you probably mean google spreadsheet not google form, right? – user0 Sep 1 '18 at 10:48
0

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

0

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

enter image description here

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

Your Answer

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

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