Conditional Formatting Problem

[polkev]

Hello,

I have created a simple three (3) page work book; one page is data, one is a report of activity report for the current day and the previous 6 and the other is a report of activity for the current day and the 29 previous days. I am using the following formula to lookup and populate the reports: =IF(COUNTIF(Date,A8)=0,"",VLOOKUP($A8,Data!A:D,2)). The report populates as expected. I want to highlight all the data in column ‘C’ of the report pages that are over 80. I set up the conditional formatting to do this, but it is also highlighting cells with a null value. I can’t use a 0 in the place of a null value because an average function is used on each report. Can I insert an argument to prevent the null cells from being highlighted? I have tried adding a second condition; e.g. ‘less then 1,’ or, ‘equal to “”,’ but neither of these arguments have worked. Can someone help?

Thanks in advance,

KP

 

[RalphA]

I believe I could help you, but, I would ask you to post your present conditional format formula. Also, what is "a cell with a null value"? Do you mean a cell with nothing in it? If so, I believe this is called "a blank cell". Once you post answers to the above, I will be ahppy to help you.

 

[polkev]

The conditional formatting reads, “Cell value is greater than 80.” The call contains the function, “=IF(COUNTIF(Date,$A9)=0,"",VLOOKUP(Data,$A9,2)).” The result of the IF function is true, “”. I would expect there to be no conditional formatting but there is.

 

[RalphA]

Assuming your answer is in cell B4, then, for your conditional formatting, use Formula Is: =CODE(B4)>80. Let us know if this fixes your problem.