Conditional formating formula

[awest181]

Been fighting this for a while, attached is a welder log. this is used to track how many welds a welder makes and when he makes them...activity log if you will. I am trying to get cells to highlight automatically on continuity log tab. working with first one on list....if 41 makes a weld in january (from weld data tab) then column E will highlight grey on continuity log. need to consider column O (welder data tab) for process to highlight accordingly on continuity log.

Taking all that into account i would also like to have the process on the continuity log highlight yellow if welder does not weld for five months, then red if welder has not welded for six months. for any of the weld processes to which it applies. (SMAW, GTAW, GMAW)

**I also have a number of formulas in hidden in the columns on weld data tab--unhide to see

[awest181]

No one knows?

[JBeaucaire]

E8: =COUNTIFS('WELD DATA'!$R:$R, E$7, 'WELD DATA'!$L:$L, $C8, 'WELD DATA'!$O:$O, $D8)
E9: =COUNTIFS('WELD DATA'!$R:$R, E$7, 'WELD DATA'!$L:$L, $C8, 'WELD DATA'!$O:$O, $D9)
E10: =COUNTIFS('WELD DATA'!$R:$R, E$7, 'WELD DATA'!$L:$L, $C8, 'WELD DATA'!$O:$O, $D10)

Now copy those 3 cells across and down the table.

This many Countifs may make your workbook a bit of a door stop.



Another approach:

1) In Weld Data in AF3: =L3&"-"&O3&"-"&R3
2) Copy that dow the data set to create a "key" column

3) In Continuity:

E8: =ISNUMBER(MATCH(C8&"-"&$D8&"-"&E$7, 'WELD DATA'!$AF:$AF, 0))
E9: =ISNUMBER(MATCH(C8&"-"&$D9&"-"&E$7, 'WELD DATA'!$AF:$AF, 0))
E10:=ISNUMBER(MATCH(C8&"-"&$D10&"-"&E$7, 'WELD DATA'!$AF:$AF, 0))

Now highlight E8:E10 and copy them together across and down as needed.

[awest181]

JB,

First thanks for your quick response. After looking at what you provided, I think we are close, see notes on cont log tab. Your formula gives me a count, what i am needing is just a conditional formating of cells. Also instead of using R row use M for date

[JBeaucaire]

Not a count, a TRUE/FALSE numeric that you can use for your Conditional Formatting. Now that you see the formulas in E8, E9 and E10 work, apply them to those same respective cells as conditional formatting formulas. Once you have the 3 cells set up with their CF formulas, copy E8:E10 as a group and paste down/across the table to apply the same "3-cell" sets of CF to the rest of the table.

[awest181]

JB,

Thanks for being patient with me and helping me get this going. Following your advice I plugged in formulas into conditional formating and they work like a champ..thanks.

However, still needing the process of the weld to shade according to time since last weld. See attached--with your formulas plugged in.

Thanks in advance

[JBeaucaire]

NOTE: I see you chose to use the first method I presented with the COUNTIFS() and no helper column on the WELD DATA sheet. Reminder, when you add that many COUNTIFS() to your sheet, you will most likely see performance issues. When that happens, switch to the second method I presented above, you just need a way to get numbers into those cells, the second method shouldn't affect speed.
------------------


1) I took the the formulas out of conditional formatting and put them back into the cells themselves. This is because your column D needs to see the results of those formulas, too, and they can't be seen by column D if they are in conditional formatting. So the formulas are back in the cells and the font color paints the results white.

2) Next I applied a new conditional formatting to those original cells that simply colors the cell grey if the value in the cell is > 0. The font is also colored grey to the number in the cell remains invisible.

3) Then I applied a new set of CF formulas to column D that color based on the last cell in that row that has a value > 0 and the month that occurred.

Since TODAY is AUG, any cells after AUG are considered to be LAST YEAR, that's why row9 is showing as red.

[awest181]

thanks JB, exactly what i was looking for............you are the MAN!!