Conditional formatting based off a few factors

seno_b

New member
Joined
Sep 1, 2012
Messages
2
Reaction score
0
Points
0
Hey all,

I've got one which has really started to beat me. I'm trying to set up a conditional formatting, but the requirement is based off a few factors. The run-down:

- The spreadsheet is to monitor the amount of times a piece of equipment has broken, and subsequently been "changed out" (to fix it).
- If an "Equipment number" (column A) and "Date of RBB break" (column E) are entered, it is considered a break (i.e. there can be situations where equipment # is entered, but no break date, meaning the entry is NOT a break)
- Column I ("Date of RBB changeout") needs to turn RED IF >=2 breaks have occurred WITHOUT a changeout

I'm doing my head in on this one and really want it finished because it's for work! Can someone smarter than me please help!
 

Attachments

  • ba4fbeb1.jpg
    ba4fbeb1.jpg
    52.4 KB · Views: 36

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Select I4
Create a New Rule using this formula. =$G4>=2 do not use an IF.
don't forget to set the fill color to red i the format.

copy down column I as far as you need it.
I used column G based on the Red you had highlited in your attached image file.
 
Last edited:

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Your explanation was not entirely clear (to me at least) what condition you were looking for. your graphic implied one thing but your explanation suggested another. Thought i would share the formula you would use if you need to test column G and H .

=AND($G4>=2,$H4=0)
 

seno_b

New member
Joined
Sep 1, 2012
Messages
2
Reaction score
0
Points
0
Hey Tommy,

Thanks for that but not what I'm after sorry.

The conditions need to be heavily based off the dates entered in "Date of RBB break" and "Date of RBB changeout". So much so I think the answer might need macros.

In the picture I have just manually entered the colour to show what SHOULD be occuring.
- The picture gives the example that there are BREAKS on 1/1/12 and 3/1/12. Now This means that a CHANGEOUT is required (because there have been >=2 breaks). In the picture there is a changeout on the 3/1/12, so there is no colour change required.
- Below this, there was a break on the 10/1/12 and on the 11/1/12, with no subsequent changeout; so those entries need to show up as red (as shown in the picture) UNTIL a changeout date is entered.

In other words, if there has been >=2 breaks SINCE the last changeout date then those new breaks should show as red (until a changeout date is entered).

Does that clear it up a bit? Sorry about the confusion but thatnks a lot for the offer of help!
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
I'm still confused ...
 
Last edited:

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
I think you'll need to base your conditional formatting on the counts of "breaks" and "breaks since changeout" in columns G and H.

Please see the attached.
 

Attachments

  • seno_b 2012-09-02.xlsx
    12.4 KB · Views: 12
Top