How to freeze values in a cell when another cell contains "Yes"

%3CLINGO-SUB%20id%3D%22lingo-sub-1029070%22%20slang%3D%22en-US%22%3EHow%20to%20freeze%20values%20in%20a%20cell%20when%20another%20cell%20contains%20%22Yes%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1029070%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20multiple%20choice%20test%20paper%20that%20marks%20itself.%20I%20am%20using%20Lists%20in%20Data%20Validation%20for%20the%20trainee%20to%20select%20%22Yes%22%20or%20%22No%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20freeze%20their%20answers%20if%20they%20select%20'Yes%22%20in%20a%20different%20cell%20when%20asked%20if%20they%20are%20want%20their%20answers%20to%20be%20marked.%20The%20idea%20of%20freezing%20their%20answers%20is%20to%20stop%20them%20%22cheating'%20by%20going%20back%20an%20clicking%20through%20the%20answers%20to%20get%20a%20pass.%20Any%20help%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1029070%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1029106%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20freeze%20values%20in%20a%20cell%20when%20another%20cell%20contains%20%22Yes%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1029106%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20may%20protect%20the%20sheet...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1029257%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20freeze%20values%20in%20a%20cell%20when%20another%20cell%20contains%20%22Yes%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1029257%22%20slang%3D%22en-US%22%3EThe%20question%20isn't%20clear%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1029817%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20freeze%20values%20in%20a%20cell%20when%20another%20cell%20contains%20%22Yes%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1029817%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F467359%22%20target%3D%22_blank%22%3E%40Nigel_OSC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20think%20of%20a%20solution%20with%20%22radio%20buttons%22%20in%20%22group%20boxes%22.%20You'll%20find%20them%20in%20the%20DEVELOPER%20ribbon%2C%20but%20it%20requires%20quite%20a%20bit%20of%20design%20to%20make%20it%20look%20nice.%20And%2C%20depending%20on%20your%20target%20group%20(how%20smart%20are%20they%20with%20Excel%3F)%20it%20could%20take%20quite%20some%20effort%20to%20make%20it%20%22fool%22-proof.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20workbook%20is%20a%20very%20quick%20and%20dirty%20example.%20It%20contains%20two%20questions%20with%20yes%2Fno%20buttons.%20The%20first%20sheet%20and%20the%20entire%20workbook%2C%20including%20a%20hidden%20sheet%20called%20%22Result%22%20are%20protected.%20Unprotect%20everything%20first%20to%20see%20what%20I'm%20trying%20to%20do%20(Tools%2C%20Protection%2C%20......)%20and%20unhide%20the%20Results%20tab%20(Format%2C%20Sheet%2C%20Unhide%20.......)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20small%20macro%20included%20that%20copies%20the%20FinalScore%20to%20the%20first%20sheet%20and%20it%20replaces%20the%20calculated%20score%20on%20the%20Result%20tab%20with%20the%20word%20%22FAIL%22.%20If%20the%20button%20is%20pressed%20more%20than%20once%2C%20FAIL%20comes%20up%20as%20the%20Score%20in%20the%20first%20sheet%20and%20you%20may%20suspect%20that%20your%20student%20tried%20to%20cheat.%20Or%20perhaps%20he%2Fshe%20accidentally%20pressed%20more%20than%20once%20without%20the%20intention%20to%20cheat.%20Who%20knows%3F%20That's%20where%20a%20lot%20of%20extra%20work%20comes%20if%20you%20want%20to%20make%20the%20system%20more%20secure.%20Up%20to%20you%20to%20decide%20how%20%22secure%22%20this%20system%20has%20to%20be%20and%20to%20decide%20if%20this%20approach%20could%20works%20in%20your%20situation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to create a multiple choice test paper that marks itself. I am using Lists in Data Validation for the trainee to select "Yes" or "No".

 

How do I freeze their answers if they select 'Yes" in a different cell when asked if they are want their answers to be marked. The idea of freezing their answers is to stop them "cheating' by going back an clicking through the answers to get a pass. Any help much appreciated.

3 Replies
Hello,

You may protect the sheet...
The question isn't clear

@Nigel_OSC 

I can think of a solution with "radio buttons" in "group boxes". You'll find them in the DEVELOPER ribbon, but it requires quite a bit of design to make it look nice. And, depending on your target group (how smart are they with Excel?) it could take quite some effort to make it "fool"-proof. 

 

The attached workbook is a very quick and dirty example. It contains two questions with yes/no buttons. The first sheet and the entire workbook, including a hidden sheet called "Result" are protected. Unprotect everything first (no passwords needed in this example) to see what I'm trying to do (Tools, Protection, ......) and unhide the Results tab (Format, Sheet, Unhide .......)

 

There is a small macro included that copies the FinalScore to the first sheet and it replaces the calculated score on the Result tab with the word "FAIL". If the button is pressed more than once, FAIL comes up as the Score in the first sheet and you may suspect that your student tried to cheat. Or perhaps he/she accidentally pressed more than once without the intention to cheat. Who knows? That's where a lot of extra work comes if you want to make the system more secure. Up to you to decide how "secure" this system has to be and to decide if this approach could works in your situation.