| I have been teaching advanced excel for many years but have never released this tidbit lets say you have to columns with almost the same data except for a few places how to compare . immediate response: next column ==> =IF(A2=A3,"","NO") but what if you want several columns to represent different test results? |  |
Advanced conditional formatting to the rescue
The biggest issue students have with conditional formatting is its inquiry of the cell it is located on, so you cannot color a cell according to another's value.INDIRECT to the rescue, INDIRECT is a function that allows you to write the address of the cell you wish to query and it will deliver the cells value.
So using concatenation and a few other text tricks:
CODE("A") will give me 65 which is the ascii number of A
CHAR(65) will return the char representation of the number 65 which is A
So CHAR(CODE("A")-1+2) will return B
COLUMN() returns the number of the column
ROW() returns the row number
If I am on row 3 column D (4)
Then CHAR(CODE("A")-1+COLUMN()-1)&ROW() will return 'D3'
INDIRECT('D3') will return the value of D3
So the following statement
=INDIRECT(CHAR(CODE("A")-1+COLUMN()-1)&ROW()) <> INDIRECT(CHAR(CODE("A")-1+COLUMN())&ROW())
Is actually C3 <> D3
which means return TRUE when the statement is FALSE since the conditional format requires a true/false response, the statement returns True when the format needs to change.
