I'm trying to make my Google spreadsheet (that is populated with textual responses from Google Forms) to color the background of the "wrong" cells red. I have the correct answer for each column at the very top in row 1. These are all alphabetical text, not numerical answers.
IF the value is NOT EQUAL to the top of the column, that cell is colored. So the "wrong" response in the below example will be colored, as it isn't the same text as the top row:
____ A ___ B ___ C ___ D __ 1 ans1 ans2 ans3 ans4 2 *wrong* ans2 ans3 *wrong* 3 ans1 ans2 *wrong* ans4
Here's where I've come across a problem. With help from AdamL, I used =AND(LEN(F5),F5<>F$1)
on the range F5:CM, which did the job for three of my spreadsheets, but two of the spreadsheets are working up.
1) Cells with the value TRUE are being universally colored.
2) Column L colors cells with the correct target answer (from L1) of FALSE.
3) The rest of the cells (from all columns) show no coloring response to the "wrong" values (values that don't match the top), only coloring the cell if has the text "TRUE".
Any ideas why this might be happening?
LINK to the spreadsheet