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 would like to use Excel's conditinal formatting of a particular column based on values in two other columns.

  • I have values in columns A, B, C and D.
  • column A has text values (any value user enters)
  • column B has a numeric value (any value user enters)
  • column C has a text value selected from a set of values (data validation using drop-down):
    • Start
    • Intermediate
    • End
  • column D is defined as (x stands for any row number)

    =IF(Cx="End";"";Bx)
    
  • user can as well delete the formula in column D thus making it empty

I want values in column A displayed in red when D is empty and user didn't select End in column C. So this only happens when a user deletes the formula in D.

So I thought of creating a conditional formatting rule that would do the trick for me. But when I try to write the condition formula it doesn't work.

I've tried specifying this formula for conditional formatting rule:

=NOT(OR(ISNUMBER(INDIRECT("D"&ROW()));INDIRECT("C"&ROW())="End"))

but it didn't work.

Are there any limitations which functions can be used in this formula, because when I use something like OR() or AND() it seems it simply stops working even though they're simple boolean functions that return TRUE/FALSE...

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Based on this Superuser question I was able to solve my problem.

Here's the solution that actually works. Replace the formula with this one:

=NOT(OR(ISNUMBER($D1);$C1="End"))

And it will work. Row number will get recalculated for each row, but columns are statically set so they'll refer to correct ones.

Even simpler

But if we consider that ISBLANK() function returns FALSE where there's a formula in a cell as well, we can simplify this even further by using this formula only:

=ISBLANK($D1)
share|improve this answer

Here is a simpler formula that does what you have asked for:

=OR($C1="End",$D1="")

To answer your question on limitations: There appear to be a few things wrong with the formula you posted

share|improve this answer
    
My version (and regionale) of Excel uses semicolon and not comma for argumet separation. I guess because commas are used in decimal numbers. Regarding the formatting functionality: I updated my question - which makes your formula to work incorrectly. But thanks for your help anyway. –  Robert Koritnik Aug 28 '10 at 23:34

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.