Conditional Formatting

[pkohli]

Hi,

I need to apply conditional formatting based on multiple conditions. for eg, If column Q is "H/W - OS EOSL" or "H/W EOSL - OS EOSL TBD" or "H/W EOSL - OS EOSL after 1yr" etc (8 such conditions) then color the background as red. Similarly, 3 conditions for yellow & 2 for green.

I tried using the OR formula but it doesn't seems to be working.

Can someone please advise how to go about it? The data in column q appears as a result of if formulas.

Thanks

 

[Richard Schollar]

Hi

You should be able to use an OR as long as you write it out like:

=OR(Q1="H/W - OS EOSL",Q1="H/W EOSL - OS EOSL TBD",...,Q1="H/W EOSL - OS EOSL after 1yr")

and not as:

=OR(Q1={"H/W - OS EOSL",...})

as you can't use array constants in CF.

 

[pkohli]

Hi,

Thanks that worked!

I have written an if statement as follows:

IF(AND($O2=$CA$1,$M2=$BZ$1),"H/W - OS EOL","")&IF(AND($O2=$CA$1,$M2=$BZ$2),"H/W EOL - OS EOL TBD","")&IF(AND($O2=$CA$1,$M2=$BZ$3),"H/W EOL - OS EOL Not Occ","")&IF(AND($O2=$CA$2,$M2=$BZ$1),"H/W EOL Not Occ - OS EOL","")&IF(AND($O2=$CA$2,$M2=$BZ$2),"H/W EOL Not Occ - OS EOL TBD","")&IF(AND($O2=$CA$2,$M2=$BZ$3),"H/W - OS EOL Not Occ","")&IF(AND($O2=$CA$3,$M2=$BZ$1),"H/W EOL TBD -OS EOL","")&IF(AND($O2=$CA$3,$M2=$BZ$2),"H/W EOL TBD - OS EOL Not Occ","")&IF(AND($O2=$CA$3,$M2=$BZ$3),"H/W -OS EOL TBD","")

Is it possible to do the above in some other way as it is long formula and difficult to make changes in it?

 

[Richard Schollar]

I would probably set up a grid with all your possible combinations like the following:

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>123</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>456</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>789</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>aaa</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value1</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value1a</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value1b</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>bbb</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value2</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value2a</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value2b</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ccc</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value3</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value3a</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value3b</TD></TR></TBODY></TABLE>

Assume it is in A1:D4

Then assume your lookup values (eg from you formula above this would involve O2 and M2) are in A7 and B7 then you need to look these up against the values in A2:A4 and B1:D1 respectively:

=INDEX($B$2:$D$4,MATCH(A7,$A$2:$A$4,0),MATCH(B7,$B$1:$D$1,0))

which will return the value at the intersection of the matched value in A2:A4 and B1:D1.

Make sense?

 

[pkohli]

Hi

I have now added a new sheet and have listed all the conditions that need to have a background as red by defining a name for this range as red. Similarly for yellow background and green.

How can I use this range in conditional formatting? i.e If A1=red, background as red, A1=yellow, background yellow else green.

 

[Richard Schollar]

Assuming you have a single column list for those values that must be red, name this list (via Insert>Name>Define) let's say you call it RedList, then in the cells that need to be conditionally formatted, apply CF as usual, change to Formula Is and use:

=MATCH(A1,RedList,0)

(this assumes that A1 is the currently active cell). Format as required.

 

[pkohli]

Thank you so much!!