I’m conducting a quiz night on Twitch. Players are submitting answers through Google Forms, which are imported into Google Sheets and automatically checked against the correct answer using this formula:
=IF(C5=$Q$4,“T”,“F”)
C5 is the team’s answer, Q4 is the correct answer, and the cell is set up as a data-validation checkbox with possible values T and F.
This works perfectly unless the team has a spelling error, etc., so the answer doesn’t match the answer key exactly - for instance, the team puts kangarooo instead of kangaroo.
So I need to be able to go to the checkbox cell and manually check the box - overriding the formula - if necessary. I know I can do by deleting the formula, but that’s a little cumbersome. Is there a way I can do it some other way? I don’t care if the formula actually GETS deleted - that’s fine - but the step of selecting the formula, deleting it, and then checking the box is too cumbersome - I’d like to be able to do it just by checking the box.
This is in Google Sheets, but I’ve found it to be functionally identical to Excel in just about every way.
I guess I can just copy “T” to the clipboard and use the keyboard shortcut to Paste Special --> Values into the cells that need to be marked correct, but that’s still not QUITE as nice as just being able to check the box.
You might solve the problem by adding another column containing an “override” and changing the formula in the data validation column to use the value in the override column if it’s not blank and do what it would otherwise do if it is. Then the original logic applies unless you enter “T” or “F” in the override column.
If I understand your suggestion, then for each team, for each question, I’d have to have an override column and manually enter T if their answer was ACTUALLY correct but didn’t match the answer key? (And then have the validation cell look to the override to see if there’s anything there first, and if not, then score it “automatically.”)
I think just doing Paste (I don’t even have to do Paste Special) to fix any answers that were marked wrong that should’ve been marked correct (I created a “T” checkbox off to the side that I can copy to the clipboard and just paste in) is probably just as efficient and quick, but I appreciate the suggestion.
I think you understand what I was suggesting, but I’m not certain so I’ll elaborate. Using your example, if you inserted a new column D to the right of column C to serve as your “answer override”, your formula would become:
=if(isblank(D5),IF(C5=$Q$4,"T","F"),D5)
Make column D another data validation column with possible values T & F, and you can correct any answer just by checking that box. If you leave D5 blank, then you have the same value as before.
Yes, I did understand, it turns out - thanks for elaborating.
That’s definitely a solution but makes my spreadsheet twice as wide as it currently is, which isn’t really what I want to do.
But, an excellent suggestion for me to keep in mind - I hadn’t thought of a manual override column, so if my copy/paste solution doesn’t really work out for some reason, I can fall back to the override column.
you might make the answer key multiple cells for each question - in one cell (Q4) put the expected answer, and use the cells to the right (R4, S4) for alternate answers. When you get a team’s answer that is deemed acceptable like “kangarooo”, paste it in R4. Your checking formula would then be =IF(ISNA(HLOOKUP(Q4:S4,1,FALSE)),“F”,“T”)
Yep, will also do that if I end up getting lots and lots of teams - doesn’t make sense to list out alternate answers when there are only 6 teams (so typos are unlikely to be repeated) but makes sense when there are 50 teams.
Since the checkboxes are based on the letters “t” or “f”, you can literally just type “t” or “f” into the cell to override it. You don’t have to click the formula, just go to the cell, type t and push enter or go another cell.
Yes, indeed I can - I was just trying to click in it. I wish I could type T or F and it would keep the checkbox validation and show it correctly (instead of the T or F letter) but yes, that works.
And the conditional formatting is super-useful as well.
Wait, now it appears that just typing T or F DOES overwrite the formula - which I thought it should way back before I started this thread. I don’t understand why it wouldn’t let me do it before. And typing the capital T DOES keep the checkbox data validation appearance.
I’m totally befuddled and feel like I started this thread for no reason at all.
Depending on the complexity of the typo’s that you want to accept as valid answers, you might use the Sounbdex algorithm to compare their answer to the correct answer. For example, both kangarooo & kangaroo have a Soundex encoding of K526, so would match.
Alas, Soundex is not a builtin function in either Excel or Libre Calc.
This thread seems to have attracted some useful Excel users, so I’m going to tack on one more question (rather than starting a new thread):
I have Google Forms import to the Google Sheet - when it imports, it creates a new Sheet in my scoring spreadsheet called “Form Responses X” where X is the number of different forms I’ve “linked” to the Sheet. So, the first Form linked to the scoresheet automatically imports to Form Responses 1.
Now, when I copy the Master Scoresheet (to use in a future week of trivia), and I link the Google Form to this new spreadsheet - let’s call it Week 1 Scoresheet - it imports the responses into Form Responses 2 because it recognizes that the scoresheet has already had a form imported into it (and it doesn’t want to overwrite that data).
It does this even if I have already deleted the Form Responses 1 sheet from the tab.
Unfortunately, all my formulas refer to the sheet titled Form Responses 1, so they all break. They still do not evaluate even if I change the name of Form Responses 2 to Form Responses 1.
They DO evaluate if I click in the formula bar at the end of the formula and simply hit “enter.” Then, the formula seems to notice that there IS a sheet with the correct name.
But that’s annoying - I have to click after each formula in a column to get it to re-evaluate, then fill that formula across each row again.
Is there any way to get around this - either some way to control how Google Forms imports, to clear out the metadata that is telling Forms that a form has already been imported so I can start fresh with Form Responses 1 again, or to change the sheet name to Form Responses 1 and get the formulas to evaluate correctly again without clicking and dragging?
Thanks much for all the help so far. I consider myself a pretty savvy Excel user for basic things but have run into some roadblocks on this one.
It would be clearer if you can share some examples, but are you asking how to get an existing Google Form (that you made for a previous quiz) to store new answers in another spreadsheet of your choosing (a new scoresheet)?
If so, go to the Google Form in question (not the sheet, the form), go to the Responses tab, click the shishkabob menu (the three dots) and choose Select Response Destination. Does that do it?
Those instructions are for linking a Form to a Sheet, which I’ve already done. The problem is that I’m linking a new form each week to a new spreadsheet, but the form doesn’t always dump the responses into the correctly-named sheet.
When the Form is completed, it automatically dumps the responses into a new sheet and names that new sheet Form Responses X, where X is the number of times a form has been linked to that sheet.
This number (the number of times a form has been linked to the sheet) seems to carry over even if I make a copy of the Scoresheet - the copy still “remembers” that a form has already dumped data into, say, Form Responses 1, so when I link a new Form to it, that new answer Form automatically dumps its data into Form Responses 2, which makes all of my formulas not work.
I don’t know if that’s clearer or not.
Here’s a link to the Scoring Spreadsheet I’m using, but I’m not sure how instructive it is to this question.
Here’s a link to the Google Sheet in View Only mode:
You can use “Named Ranges”, which is basically a way to store (and modify) a range of cells to use in your formulas.
So let’s say you make a named range, and call it CurrentAnswers, pointing to all the answers from Week 1. Your formulas all point to CurrentAnswers!B4:B59 instead of Form Responses 1!B4:B59.
Then, when week 2 rolls around, you just update the CurrentAnswers named range to Form Responses2!B4:B59 and your formulas will all be updated. (Meaning you just have to change that one named reference, since all the formulas point to that named range instead of the ranges directly.)
Another way to do it is to use the “indirect()” function, which lets you point to a cell by name (an exact “string” that you enter) instead of by Gsheet’s internal reference. Every scoresheet can have one cell (A1) that just has the name of the correct range, like the literal text “Round 4 Responses!B:B”. And then all the scoring formulas point to indirect(A1). You update A1 for every scoresheet each week.
I updated my example with a new sheet demonstrating this:
It happens. Tech is finicky.