Help with Conditional Formating For Creating Multiple Choice Drop Down Tes

[dn7309]

So I am creating a multiple choice drop down test in excel and I want to make it where the cell would change to the color green if I get the answer right and red if I get it wrong. I know how to do it for each cell individually but that would take a VERY long time.

So what I thought I could do is make a copy of the column in a another spreadsheet (Label Answer) and make column in the test page (Label Test) change color based on the answer on the other spreadsheet.

Who what I did was create a new rule in the on the Answer column in "Test" page to change to the color green if the text matches the cells in the on the answer column in the "Answer" page in the same excel file.

the problem is that this only work on the first question, any question after that doesn't change color at all.

This is all done using the "format only cell that specific text"

Here is my example for anyone that can help me out.

https://www.dropbox.com/s/ncqt802s230eds7/Example%20Test.xlsx?dl=0

 

[Mr_Olaf]

Hello dn7309,

I downloaded your workbook, and I think I solved your problem. You created named range "Answer" for the range B1:B10 in sheet "Answer". In order to match answers with questions, you 'll have to create another named range "Question" for the range A1:A10 in the same sheet.
In "Test" sheet add two new rules for conditional formatting in column B with folowing formulas (Rule type: "Use a formula to determine witch cells to format"):
Rule for wrong answer (red color): =AND(NOT(ISBLANK($B1)),INDEX(Answer,MATCH($A1,Question,0))<>$B1)
Rule for correct answer (green color):=INDEX(Answer,MATCH($A1,Question,0))=$B1

I hope this will help you.

 

[dn7309]

Thanks for the response. Unfortunately the formula did not work. I've was told to use the VLOOKUP function. I will have to mess around with that to see if it works. Thanks for the help.

 

[Mr_Olaf]

Combination Index and Match in above formulas is instead of Vlookup. You can write formulas with Vlookup function:
Rule for wrong answer:=AND(NOT(ISBLANK($B1)),VLOOKUP($A1,Answer!$A$1:$B$10,2,FALSE)<>$B1)
Rule for correct answer:=VLOOKUP($A1,Answer!$A$1:$B$10,2,FALSE)=$B1

You have problem with something else.