Conditional formatting using the INDIRECT function fails with boolean AND or OR or with cells containing formulas

excel excel-formula excel-2013 excel-r1c1-notation
Abel picture Abel · May 23, 2017 · Viewed 8.2k times · Source

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

ian0411 picture ian0411 · Sep 27, 2017

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")

Related questions

If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
Excel: Searching for multiple terms in a cell

I use this handy equation to search for a term inside of a cell in excel. =IF(ISNUMBER(SEARCH("*Gingrich*",C1)),"1","") This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1. All I'd like …

Read More
Separate octets of IP address using formulas

I want to separate out the octets of an IP address using Formulas. I have tried some things like substitute & find but cannot figure this out. Example of what I want to achieve, by only starting with Cell A1 …

Read More