Conditional Formatting Nested Formulas
 

I'm working with a large spreadsheet that has multiple status options. Each
status has different criteria for example:

Status Green Yellow Red
T <60 =60,<=80 80
S <20 =20,<=45 45
D <30 =30,<=55 55
C <90 =90,<=120 120
B <30 =30,<=60 60
A <10 =10,<=40 40

I would like to apply conditional formats according to the status criteria.
Is that possible given that each status has a different criterial. Thank you!

Conditional Formatting Nested Formulas
 

One way to get going here ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3jddl
Complex Criteria CF.xls

Construct:
In a sheet: x,
Set up the param table below in A2:C7
T 60 80
S 20 45
D 30 55
C 90 120
B 30 60
A 10 40

Then in your data sheet,

Assume the key col values are in B2 down, Statuses (T,S,D etc) in C2 down
Using 4 adjacent cols to the right, say cols D to G
Enter these labels into D1:G1 : Colour, Green, Yellow, Red

Put in, and array-enter,
ie press CTRL+SHIFT+ENTER [CSE] to confirm each formula:
D2: =INDEX($E$1:$G$1,MATCH(TRUE,ISNUMBER(E2:G2),0))
E2: =MATCH(1,(C2=x!A$2:A$7)*(B2<x!B$2:B$7),0)
F2: =MATCH(1,(C2=x!A$2:A$7)*(B2=x!B$2:B$7)*(B2<=x!C$2 :C$7),0)
G2: =MATCH(1,(C2=x!A$2:A$7)*(B2x!C$2:C$7),0)

Select D2:G2, copy down to the last row of data. Col D will return the
required "colour" for each data row. Hide away cols E to G as desired. You
could then easily point to col D's colour values to conditionally format as
desired.

Take care to visually see that the top cells D2:G2 are correctly
array-entered before you copy down. Look in the formula bar, each formula
should be wrapped with curly braces by Excel: { }. If you don't see the
curlies, that means it wasn't correctly array-entered. Click inside the
formula bar, and re-do the CSE.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Marissa" wrote:
I'm working with a large spreadsheet that has multiple status options. Each
status has different criteria for example:

Status Green Yellow Red
T <60 =60,<=80 80
S <20 =20,<=45 45
D <30 =30,<=55 55
C <90 =90,<=120 120
B <30 =30,<=60 60
A <10 =10,<=40 40

I would like to apply conditional formats according to the status criteria.
Is that possible given that each status has a different criterial. Thank you!

Conditional Formatting Nested Formulas
 

Here's a slightly different approach......

Assuming the same table as Max, but in D2:F7 in the same sheet as your data,
then with status in column A and value in B, select the column(s) you want to
format and use these conditions

condition 1
=$B1VLOOKUP($A1,$D$2:$F$7,3,0)*ISNUMBER($B1)
format red

condition 2
=$B1VLOOKUP($A1,$D$2:$G$7,2,0)*ISNUMBER($B1)
format yellow

condition 3
=ISNUMBER($B1)
format green

"Max" wrote:

One way to get going here ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3jddl
Complex Criteria CF.xls

Construct:
In a sheet: x,
Set up the param table below in A2:C7
T 60 80
S 20 45
D 30 55
C 90 120
B 30 60
A 10 40

Then in your data sheet,

Assume the key col values are in B2 down, Statuses (T,S,D etc) in C2 down
Using 4 adjacent cols to the right, say cols D to G
Enter these labels into D1:G1 : Colour, Green, Yellow, Red

Put in, and array-enter,
ie press CTRL+SHIFT+ENTER [CSE] to confirm each formula:
D2: =INDEX($E$1:$G$1,MATCH(TRUE,ISNUMBER(E2:G2),0))
E2: =MATCH(1,(C2=x!A$2:A$7)*(B2<x!B$2:B$7),0)
F2: =MATCH(1,(C2=x!A$2:A$7)*(B2=x!B$2:B$7)*(B2<=x!C$2 :C$7),0)
G2: =MATCH(1,(C2=x!A$2:A$7)*(B2x!C$2:C$7),0)

Select D2:G2, copy down to the last row of data. Col D will return the
required "colour" for each data row. Hide away cols E to G as desired. You
could then easily point to col D's colour values to conditionally format as
desired.

Take care to visually see that the top cells D2:G2 are correctly
array-entered before you copy down. Look in the formula bar, each formula
should be wrapped with curly braces by Excel: { }. If you don't see the
curlies, that means it wasn't correctly array-entered. Click inside the
formula bar, and re-do the CSE.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Marissa" wrote:
I'm working with a large spreadsheet that has multiple status options. Each
status has different criteria for example:

Status Green Yellow Red
T <60 =60,<=80 80
S <20 =20,<=45 45
D <30 =30,<=55 55
C <90 =90,<=120 120
B <30 =30,<=60 60
A <10 =10,<=40 40

I would like to apply conditional formats according to the status criteria.
Is that possible given that each status has a different criterial. Thank you!