Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm having an issue with conditional formatting using the INDIRECT function. I'm doing something similar to Using INDIRECT and AND/IF for conditional formatting , but the only answer there isn't working for me.

Basically, I want to highlight rows where B is not blank and F is blank. INDIRECT will work for ONE of the conditions, but

= AND(INDIRECT("B"&ROW()) > 0, INDIRECT("F"&ROW()) = "") 

does not work at all.

The answer in the question points to replacing the references with relative ones, so I'm thinking this should work:

= AND ($B2 > 0, $F2 = "")

But it does not, nor does ISBLANK($F@) or ISEMPTY($F2) (the cell contains a formula that sometimes will return "", I want the row highlighted in these cases but only when something is in column B).

Am I missing something about relative references? Why doesn't INDIRECT work with AND/OR?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

I don't see any need for INDIRECT, "not blank" is <>"" so perhaps try

=AND($B2<>"",$F2="")

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.