Conditional formatting using the INDIRECT function

Question

I have the following function for checking whether column L contains the word "completed" and I use INDIRECT to be able to color the whole row with Conditional Formatting:

 =INDIRECT("l"&ROW())="completed"

This function works. However, I need to extend this, I want to use Conditional Formatting based on an extra cell as well, so I tried this:

=AND(INDIRECT("l"&ROW())="completed";INDIRECT("m"&ROW())="duplicate")

When I use this second function inside the Excel worksheet they give the proper TRUE or FALSE.

Furthermore, I needed a Custom Formatting on the result of a formula in a cell. I tried the following:

=INDIRECT("n"&ROW())=123456

This only worked if I removed the formula in the cell with the result itself as a number. Again, the function worked when pasted in an worksheet cell.

Is there a way to make this work inside Excel or is there a limit to what Conditional Formatting functions can do?

In case you ask: AND(1;1) works and makes everything yellow, AND(INDIRECT("n"&ROW())=123456;1) does not work, nor does replacing AND with OR.

The semicolon is because I am in the Dutch locale. Replace it with a comma if you are in an English locale.

Answer 1

You have to think in terms of xlR1C1 formulas to understand CFRs. A CFR based on a formula thinks of it as =RC12="completed" or more completely =AND(RC12="completed", RC13="duplicate").

The xlR1C1 formula does not change no matter what cell you paste it to; it is in this way that CFRs can be applied to a wide range of cells without expending calculation cycles to update the formula for each individual cell. RC12 means 'the cell in column L on the row you are on'. It does not change if filled down, filled right or copied to any other location.

Now unless you are actually working in xlR1C1 (File, Options, Formulas, Working with Formulas, R1C1 reference style) you have to convert the xlR1C1 to xlA1 style. If you are applying the CFR to a number of rows starting with the first row then the R becomes 1 and the C12 becomes $L.

'xlR1C1
=AND(RC12="completed", RC13="duplicate")
'xlA1
=AND($L1="completed", $M1="duplicate")

If you were applying the CFR to a range starting in row 2 change the $L1 to $L2 and the $M1 to $M2.

---

_{Among other reasons for not putting the xlR1C1 style formula directly into the CFR creation dialog when working in xlA1 style is that there actually is a RC12 cell in xlA1.}

Answer 2

Not sure why this wouldn't work in Conditional Formatting. But you can simply replace the AND function with * such as:

=(INDIRECT("l"&ROW())="completed")*(INDIRECT("m"&ROW())="duplicate")