Excel 2003 - Conditional Formatting On Cells with Formulas 2

[kjv1611]

A coworker of mine wanted to know how to do conditional formatting, but was unsure of what it was called, etc. So I showed him.

Well, then he came to me with a question, which I find perplexing. Basically it's driving me batty, b/c surely there's a simple answer/solution, but I just can't seem to get it worked out.

I have Office 2007 on my machine currently, and he has Office 2003, so I'd prefer methods that will work with 2003 specifically if possible.

Here's the situation.

He's got a Column which gets the difference in 2 dates (each cell) IF another cell has a value.

Here's a sample of the values, and the function:

Cells:[TT]
Row# Column D
29 Current Date
30 5/27/2009
31 7/25/2009
32 6/14/2009
33 7/15/2009
34
35
[/TT]

Cell C39 is a static date. The value is 7/31/2009

The formula in Cell H29 is:

=IF(ISBLANK(D29)," ",SUM($C$39-D29))

Simple enough formula, I think.

The problem comes with the conditional formatting.

He tried these 2 conditions, and has a 3rd to add:
They are the same for Range ($H$29:$H$38)
[tt]
Condition 1:
Cell Value > 20 Fill Red
Cell Value between 15 AND 20 Fill Yellow
[/tt]

That seems simple enough to me.

However, when he has a blank field in Column D, it's formatting the cell in the same row, column H with a red fill.

My first guess was that we could put a formula in the conditional formatting, but is there not a way to make the conditional formatting not do anything with blank values, other than a formula?

I did try looking at CPearson's info here:

http://www.cpearson.com/excel/cformatting.htm

But unless I overlooked it, I don't see any good answer for this. Surely it's a common issue that I just can't seem to find the right solution.

Any advice/examples/references would be greatly appreciated.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Thanks, I'll give it another look today, and see if I can get it working correctly.

Will post back once I've gotten back to it.

--

"If to err is human, then I must be some kind of human!" -Me

 

[combo]

If the target is a printed report only, you can replace
=IF(ISBLANK(D29)," ",SUM($C$39-D29))
by
=IF(ISBLANK(D29),NA(),SUM($C$39-D29))
and in page setup, sheet tab, set print errors to blank.


combo

 

[Gavona]

How about:
Condition1: Cell Value = ""
Condition2: Cell Value > 20
Condition3: Cell Value > 15
(CF stops processing conditions as soon as it finds one that is met)

Gavin

 

[kjv1611]

Alright, it seems that the problem was the $ usage (Excel default) in the formulas. I put the formula in again, took the $ signs out, and it now does not format the blank cells just as [blue]lionelhill[/blue] observed.

Since that's working, I don't see the need to add in the 3rd condition of conditional formatting to handle the BLANK cells... especially since my coworker actually may want 3 different conditions anyway.

As for printing, I don't think it will be only for printing. But thanks for that thought as well, [blue]combo[/blue].

Thanks to everyone for helping me get this one fixed. It was bugging me, and now I feel like I've increased my knoweldge with Excel. Lets just hope I don't forget it by the time I need it again!

--

"If to err is human, then I must be some kind of human!" -Me