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

2 Answers 2

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 issue could be where you start to apply the conditional formatting. The answer with = AND ($B2 > 0, $F2 = "") assumes you are applying your conditional formatting row 2 of any column.

For example if you are applying your conditional formatting rule to an entire column the first row that the rule is applied needs to be the row number in your formula. You would simply highlight the entire column then type = AND ($B1> 0, $F1 = "")

If you are applying the formatting to G5 through G7 this is what you would input. = AND ($B5 > 0, $F5 = "")

Relative references will start where your formula starts then proceed to copy itself across the range you are applying the rule to. That's why the $ isn't present on the number. So the rule can move down with your range.

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.