I am trying to use conditionally format row of cells if the value of the cell in column A is between 1 and 5 but not blank. The simple expression,
=INDIRECT("A"&TEXT(ROW(),"0"))<=5
produces "TRUE" and in conditional format will format all rows, unfortunately also those with 0 or nothing in column A.
The expression,
=AND(INDIRECT("D"&TEXT(ROW(),"0"))>0,INDIRECT("D"&TEXT(ROW(),"0"))<=5)
does produce TRUE only if the value of the cell in column A is between 1 and 5 but not blank, but does not work in conditional formatting, at least in Excel 2007.
The expression,
=(INDIRECT("D"&TEXT(ROW(),"0"))>0)*(INDIRECT("D"&TEXT(ROW(),"0"))<=5)
also produces the value 1 (TRUE) only if the value of the cell in column A is between 1 and 5 but not blank, and it does work in conditional formatting.
There are other alternatives to solving the problem, such as using the combination of
=INDIRECT("D"&TEXT(ROW(),"0"))=0 [check the stop if true box]
=INDIRECT("D"&TEXT(ROW(),"0"))<=5
BUT why doesn't the AND expression work? Some solutions in the forum do suggest the the use of AND and are reported to work. I downloaded a the example file AF010235700.XLSX and the AND expression doesn't work for me in that file either.
Am I missing some setting in my computer or program? I have SP3 installed. http://www.excelforum.com/images/smilies/confused.gif
Bookmarks