Conditional Formatting - Using AND Formula

[rsbrunton]

Problem
When extending conditional format formula to use AND(), formatting is not applied

Example Using Excel 2007
Cell A1:
Contains formula "=INDIRECT(ADDRESS(ROW(),2))=100"
Conditional Formatting set to fill color to Green if "=INDIRECT(ADDRESS(ROW(),2))=100"
Cell B1:
Contains value of "100"
--> Result: A1 evaluates to TRUE and fill color is Green ... cool!

Cell A2:
Contains formula "=AND(INDIRECT(ADDRESS(ROW(),2))=100,TRUE)"
Conditional Formatting set to fill color to Green if "=AND(INDIRECT(ADDRESS(ROW(),2))=100,TRUE)"
Cell B2:
Contains value of "100"
--> A2 evaluates to TRUE, but fill color is not Green ... doi!

Cell A3:
Contains formula "=AND(TRUE,TRUE)"
Conditional Formatting set to fill color to Green if "=AND(TRUE,TRUE)"
--> A3 evaluates to TRUE, and fill color is Green ... cool!

What am I missing here?
Are there issues of formula complexity within Conditional Formatting?
Online documentation of other nuances anywhere?

My thanks in advance.

 

[Jerry Sullivan]

That's interesting. The CF formula appears to be returning an error.
CF Set fill color Green if "=ISERROR(AND(INDIRECT(ADDRESS(ROW(),2))=100,TRUE))"
Fills cell with Green.

Similar results with OR()

Based on T.Valco's (MrExcel MVP) response in this thread, this doesn't appear to be a known problem or limitation.
http://www.mrexcel.com/forum/showthread.php?t=604433

As a practical matter, is there a reason you need to use INDIRECT in the CF formula?
INDIRECT seems to be a clever workaround to the maximum 3 CF rules limitation prior to xl2007. Without that 3 rule limitation, can you accomplish your task using relative referencing in the CF Formula?

 

[Jerry Sullivan]

Just noticed that I botched the spelling of T.Valko's screen name.
My apologies Biff.

 

[rsbrunton]

Jerry ... much thanks for taking an interest in this situation.

Your reply suggests that the 'middle' example generated a error in the cell ... it shouldn't:

Cell A2:
Contains formula "=AND(INDIRECT(ADDRESS(ROW(),2))=100,TRUE)"
Conditional Formatting set to fill color to Green if "=AND(INDIRECT(ADDRESS(ROW(),2))=100,TRUE)"
Cell B2:
Contains value of "100"
--> A2 evaluates to TRUE, but fill color is not Green ... doi!

The INDIRECT(ADDRESS()) is meant to reference Cell B2.
The use of the OR() is what, I believe, is the cause of the CF failure.

I can send an example Excel file, if that would help make the situation concrete.

 

[Jonmo1]

It's the combination of AND and ROW (beyond row 1 for some reason)..

This works in A2
=INDIRECT(ADDRESS(ROW(),2))=100
This Doesn't work in A2
AND(INDIRECT(ADDRESS(ROW(),2))=100,TRUE)
This DOES work in A2
AND(INDIRECT(ADDRESS(2,2))=100,TRUE)

I agree though that this is an interesting topic, but I would also like to know why you want to use indirect in CF in the first place..

why not just use
=AND(B2=100,TRUE)

 

[Jonmo1]

It's also the combination of AND INDIRECT and ROW..

Avoid indirect, you can do the same thing with INDEX

Try

=AND(INDEX($B:$B,ROW())=100,TRUE)

 

[Jerry Sullivan]

Your reply suggests that the 'middle' example generated a error in the cell ... it shouldn't:

I agree it shouldn't - but it does! That's what makes it interesting.

As jonmo1 suggests, using either INDEX or relative addressing would hopefully provide workable alternatives to INDIRECT.

 

[rsbrunton]

Jerry ... thanks. Is there a way to share a small Excel file? If it can be included in the post, that may help.

Given that, I would like to know how to drive this along for deeper investigation.

 

[Jerry Sullivan]

Jerry ... thanks. Is there a way to share a small Excel file? If it can be included in the post, that may help.

Given that, I would like to know how to drive this along for deeper investigation.

This forum doesn't support attachments- when necessary people either post to a sharing site like Box.com or exchange email addresses through a Private Message (PM).

I don't think it's necessary to see your actual file to understand the problem though, because the problem is easily repeatable from your description- which jonmo1 and I confirmed.

So at this point, I'd suggest you consider the combination of Row() and Indirect() in a CF formula an Excel bug; and focus on finding alternatives to accomplish your task.

If you'd like some help with that, you can continue this thread with a screen shot that better represents that actual task you are trying to perform. If you use one of the Screen Shot methods listed in my signature block, it will make it easier for people to assist you.

 

[rsbrunton]

Thanks to both Jerry and jonmo1 ... I'll accept this as Excel bug.

Much appreciated for providing the INDEX() workaround ... nice!

PS: This is my 2nd experience with MrExcel and both have been stellar.