Conditional formatting with a named cell

[dnessim]

Hi,

I have a conditional format that works fine but what if I want to assign an named cell as my criteria instead of hardcoding a value. Seems like this should work?


Hardcoded version:

=SUMPRODUCT((MONTH(Entry_Date)=2)*(YEAR(Entry_Date)=2013)*(Indicator_Name="CLABSI")*(Facility="Carrollton")*(Unit_Group="ICU")*(SIR))>2


What if I want the value ">2" to be in a named cell so I can change it if needed:

CLABSIGREEN is the name of my named cell


=SUMPRODUCT((MONTH(Entry_Date)=2)*(YEAR(Entry_Date)=2013)*(Indicator_Name="CLABSI")*(Facility="Carrollton")*(Unit_Group="ICU")*(SIR))& CLABSIGREEN


I have tried

= CLABSIGREEN


thanks Dave

 

[Luke M]

It can be done, but we need to use the "ancient" Evaluate function. However, Evaluate is only available in VB and the Named Ranges. So, first, let's define a Named Range called "MyCondition" with this definition:

=EVALUATE(SUMPRODUCT((MONTH(Entry_Date)=2)*(YEAR(Entry_Date)=2013)*(Indicator_Name="CLABSI")*(Facility="Carrollton")*(Unit_Group="ICU")*(SIR))& CLABSIGREEN)


Now, for the conditional formatting, define the formula to be:

=MyCondition


Things should work now, and you can change the CLABISGREEN to be any valid Boolean comparison.

 

[dnessim]

Luke,

I will have to try this out, I guess maybe I should rebuild my whole project in VBA.

My formulas are getting so big they wrap 3 lines in the formula bar.

So I should be able to write all my formulas in VBA and wrap them with a Evaluate function? I give this a shot

Thanks

Dave

 

[Luke M]

I don't think I can recommend that. Running in VBA is typically slower than running natively. The above solution doesn't require VB, it's done using a Named Range (under the formula ribbon). I only mentioned VB because that and Named Ranges are the only places you can use the EVALUATE function. It won't work in a normal worksheet formula.


However, there may be other ways to simplify you formulas. For instance, this little bit

(MONTH(Entry_Date)=2)*(YEAR(Entry_Date)=2013)

Can be simplified (and made more robust) by:

(TEXT(Entry_Date,"MMYYYY")=022013)

Let's you do both criteria checks in one go, and the TEXT function can handle non-date cells w/o causing an error.