Hiding Results of Answers for a Quiz

[ScottishSteve]

Hi,

Looking for some help here please.

I currently have a quiz set up, with simple questions in the A Column.

The delegate places their answer in the B Column, and the C Column then
states whether it is correct or not.

Used the following formula :
=IF(B2="A","CORRECT",IF(B2="","",IF(B2="B","INCORRECT")))

What I need, is for the formula to only be applied once the delegate
indicates that they are finished the whole test. (i.e. clicks a cell marked
as a "Finished" button).

I need to know 2 things:

1. How to set up a cell like a "finished" button for example.
2. How to get the formula not to apply until the finished button has been
selected.

Any help would be greatly appreciated!!

Thanks!

 

[Bernie Deitrick]

Steve,

Put another sheet into your workbook, with an answer table - put the correct answers in the same
relative cell as on your questions sheet. For example, if the question "What is the sum of 2 and 3?
A= 4, B = 5, C = 6" is in cell A3 of the questions sheet, the answer B should be in cell A3 of the
answers sheet. Name the sheet Answers, then hide the worksheet and protect the workbook structure
with a password. Make sure that the cells where the answers are entered are unlocked - format
cells, protection tab, un-check locked.

At the bottom of your quiz, name a cell ShowAnswers, and use data validation to limit the values to
Done! or Working... using the list and dropdown options. Color the cell green, and put Working...
into the cell as the initial value.

Then in cell C2, use the formula

=IF(ShowAnswers = "Done!",IF(B2=Answers!B2,"CORRECT","INCORRECT"),"")

and copy down to match your questions. When the quiz is done, have the delgate select the
ShowAnswers cell and Enter Done! from the dropdown, and the Correct/Incorrect strings will show.
You could highlight the wrong answers by using a slightly different formula:

=IF(ShowAnswers = "Done!",IF(B2=Answers!B2,"","INCORRECT"),"")

or by using Conditional formatting the color the correct values green and the incorrect values red.

HTH,
Bernie
MS Excel MVP