Impossible Conditional Formatting Problem?

**noworriespete** · 01-08-2013, 02:44 PM

I am a teacher and need to do some different data analysis on student assessment data. I can't get excel to do what I want, hopefully someone will be able to help me find a solution to my problem.

Here's what I want to do:

Conditionally highlight the correct answers to the data output of the program I am using. Here is a sample of the data:

Q1 Q2 Q3 Q4 Q5 Q6 Q7
A D Yes B D C D
B A Yes D C D A
A D No A B D D
B C Yes D D B B
D C No B D A B
B D No B D C A
D B No A D C B
B D Yes D C B A
B C No B D B B <<<<Answer Key

I want to easily highlight the correct answers, using an answer key on the bottom row. I can get it to highlight one column using a conditional formatting rule, but I cannot get it to do the entire document without manually doing each column individually. I hope there is something simple that I am missing and can do. I don't have hours to spend trying to figure this out, besides.

Thanks in advance!

Pete

**TMS** · 01-08-2013, 02:54 PM

I would suggest that you post a sample workbook. I'm sure someone will work on it and post it back.

Personally, I'd suggest you put the key somewhere else, maybe on another sheet.

Regards, TMS

**TMS** · 01-08-2013, 04:33 PM

Thanks for the rep. Are you on the way to a solution? If you need more help, please post a sample workbook.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**noworriespete** · 01-08-2013, 04:42 PM

You are right, if I move the key to a different page I can set up a template that reads the key and applies the rule to the data. Thanks for the thought!