Excel conditional formatting based on a formula

Question

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...

Answer 1

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

• The semi-colon should be a comma
• INDIRECT() is a problem in conditional formulas, and you don't need to use it anyway.
• It doesn't appear to actually do exactly what you have specified that it should do?!

Answer 2

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)