Conditonal Formats

[LGMan]

Hi all, I've been working on a worksheet that has a combo box which has a list of measures, so you can select one at a time, and then monthly results for that chosen measure are shown on the sheet. Within a row above the data I have the monthly targets.
The measure description appears in A4, e.g Employee Sickness
So Targets are in B4:M4
Data appears in A5:M8, where A5 is 'North' A6 is 'East', A7 is 'South' and A8 is 'West'
Cells B5:M5 would show the monthly result for North and so on.


I'd like to be able to use conditional formats to use Red or Green to denote 'On Target' or 'Adverse to Target'. The trouble is, some of the measures have targets where the aim is to get lower then it, say Employee Sick Days, or others where the aim is to get higher than the target, such as 'Call handling within 10 seconds'

Previously all the measures where listed out at the same time, as were the separate targets, so I was able to use simple CFs such as Cell B5 Greater then B4 is Green.

Hope all that makes sense, a little pointer will help, thanks in advance

 

[LGMan]

Haha,
I've worked something out...
=IF(($A$4)="EmpSL",B$5>=B$4)
and just keep building new CFs per measure

Is it possible to have more than one value for $A$4.
Like =If $A$4 = "EMPSl" or "Failed Calls", B$5>=B$4)

 

[dhookom]

I typically use a multiplier of 1 or -1 in a cell. Use a reference to this cell in your conditional formatting expression.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Hi,

I'd still use Excel's Conditional Format feature.

So the CF formula is based on the value in A4 (the from the combo), and therefore you might have 2 or more OR expressions. What's contained in the OR depends on the complexity. It seems that ONE value has one type of expression and the remainder has the other type of expression.

So some $A$4 equals some value AND x>y OR $A$4 not equal some value AND x<y.

 

[LGMan]

Hi Skip, there are 12 measures, 5 of them are based on being above a Target figure, while the other 7 are bases on being below the Target figure.
I was hoping to cut down on the CFs by having 5 expressions in one CF and 7 in the other CF.

 

[SkipVought]

I'd make 2 lists

=OR(AND(MATCH($A$4,List1,0)>0,x<y),AND(MATCH($A$4,List2,0)>0,x>y))

 

[LGMan]

Thanks Skip