4

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.

Abel
  • 56,041
  • 24
  • 146
  • 247
  • 1
    It is unclear on why you require `INDIRECT`. `INDIRECT("l"&ROW())` is better as `INDEX($L:$L, ROW())` but you should be able to just use `$L1` if your conditional formatting area starts in row 1 or `$L2` if your conditional formatting area starts in row 2. –  May 23 '17 at 16:48
  • @jeeped, the conditional formatting is applied to all rows, so using L1 would then only apply to the first row and I would need to create a different rule for each row. That advice came from another So question. But if there's another way, I'd love to try it! – Abel May 23 '17 at 16:51
  • 1
    No, that's not how conditional formatting works. You enter the formula for the upper left cell of the range and Excel will then apply the modified formula to other cells in range, just as if you copied it there. Try it. – martin May 23 '17 at 16:54
  • 1
    @Abel no, by using `$L1` the row will change while the column will remain the same. The `$` locks the Column as absolute and the lack of `$` in front of the row allows it to be relative. – Scott Craner May 23 '17 at 16:55
  • Change the "applies to" range to multiple rows example: =$1:$21 for rows 1 to 21 – Nitesh Halai May 23 '17 at 16:56
  • @scott and others, thanks, I'll try that. Still odd that this function only works when used on its own though and not when combined – Abel May 23 '17 at 17:02
  • There is a priority to conditional formatting make sure you have the second in higher priority to the first. – Scott Craner May 23 '17 at 17:04
  • 1
    ... and Stop If True checked if you do not want CFRs further down the list to counteract. –  May 23 '17 at 17:09
  • Try using this formula: =AND($L1="Completed",$M1="Duplicate") instead of the Indirect formula. For some reason the indirect formula doesn't seem to work for me as well. – Nitesh Halai May 23 '17 at 17:28

2 Answers2

4

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")
ian0411
  • 4,115
  • 3
  • 25
  • 33
2

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.

  • Your last comment was a tad confusing. Since I wanted to skip the first row (it contains headers), I used `$L2="x"`, but this turns out to format the row above the row with that condition (as in: for every row, it will look at the next row for that value in that column, and color the original row). Other than that: it works! Thanks! (side note: still odd that `INDIRECT` doesn't work at all, as user Nitesh mentioned, and as was suggested here: https://stackoverflow.com/a/13865032/111575). – Abel May 23 '17 at 18:03
  • And as an aside, if you use `L2` instead of `$L2`, only the first cell will get colorized. Other than that it works the same. – Abel May 23 '17 at 18:04
  • 1
    OK, if the **Applies to:** area for the CFR starts in row 1 then use $L1; if it starts in row 2 then use $L2. The reason L1 doesn't work is because it is not RC12, it is RC which does not equate to $L1 but A1. Tap F5 (GoTo), type RC12 and hit enter. You will go to the 12th row of the 471st column; in xlA1 this is RC12, in xlR1C1 it is R12C471. –  May 23 '17 at 18:10
  • Yes, the applies-to area only selected the applicable columns, so that would automatically include the first row, of course. This discussion has given me some valuable insight, many things just got easier, thanks again! – Abel May 23 '17 at 18:27