Prev: Highlight a Cell if Data not Present
Next: AverageIF
From: knechm1 on 10 Mar 2010 11:20
Hello,

I am hoping someone can help me out. I have a large spreadsheet which
records various data regarding rejected parts. I want to include a
function/conditional formatting that will count if there is more than one
rejected part number in the last 90 days. There is a date column where the
date of rejection is entered. I would like this "part number" cell to turn a
different color to identify multiple rejections. Can someone help?

From: ck on 11 Mar 2010 01:25
You can try to see if this works the way you want. But you will need a helper
column. Assume the following:

A B C
1 Date Part
2 01/03/2010 a
3 01/02/2010 b
4 01/01/2010 a
5 15/02/2010 c
6 01/11/2009 c

In C2, paste this =IF(AND(A2<TODAY(),A2>TODAY()-90),B2,"")
Drag till the last row and it will give you a list of those parts that are
rejected in the last 90 days.

In B2, enter this into the condition formatting =COUNTIF($C$2:$C$13,B2)>1
Format it as you like. You need to drag the formula down, so you might have
to cut and paste your existing parts to another location and paste it back
after the formula has been entered into column B

Let me know whether it works.

"knechm1" wrote:

> Hello,
>
> I am hoping someone can help me out. I have a large spreadsheet which
> records various data regarding rejected parts. I want to include a
> function/conditional formatting that will count if there is more than one
> rejected part number in the last 90 days. There is a date column where the
> date of rejection is entered. I would like this "part number" cell to turn a
> different color to identify multiple rejections. Can someone help?
>
 | 
Pages: 1
Prev: Highlight a Cell if Data not Present
Next: AverageIF