soooooooooooo i'm having trouble with sumproduct and conditional formatting. on one tab i have a list of static data as a named range ("Legal_Entity," "Deal_Name," etc.), and on the next tab I am setting up a template for inputting Legal Enitities, Deal Names, etc. I'm trying to get it to format the cell if the Deal name provided doesn't exist on the other tab or it doesnt correlate to the legal entity on that line. I thought sumproduct would work witha Key column taht was justa bunch of 1s.
here's the setup:
Named Ranges: Deal_Name, Legal_Entities, Key
Deal column: F:F
Legal Entity Column: E:E
My thought on the formula: =if(sumproduct((Deal_Name=F2)*(Legal_Entities=E2)*(Key))>0,TRUE(),FALSE())
that didn't work in the conditional format,s o i took it out, and it still didn't work. i tried just the sumproduct with the named range, and it gave me 0. i changed the named ranges to the actual ranges and it gave me the result i wanted.
please help!