Formulas in Conditional formatting

[ccheever2009]

I am trying to build conditional formatting for a report I run daily. There are certain criteria that I want to "pop" out drawing immediate attention.

I want build it so that the variables in column O (several) are true column W (several) are true and Column if Blank

=AND($O1="BR- PAP New Setup: Canton", $W1="Harvard pilgrim",$Y1="")

This works but I want to add more data to $o1 and $w1 but every time I do I receive a function error.

Any help greatly appreciated.

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Do you mean something like this?

=AND(OR($O1="BR- PAP New Setup: Canton",$O1="Some other text"),OR($W1="Harvard pilgrim",$W1="Some other text"),$Y1="")

 

[ccheever2009]

I tried this, but for some reason any time I try to enter something into $O1="some other text" I keep receiving an error. I even cut the formula out and put into word, to build making sure everything was correct.

 

[ccheever2009]

=AND(OR($O1="BR- PAP New Setup: Canton",$O1=“BR- PAP New Setup: Kittery”,$O1= “BR- PAP New Setup: Sandwich”,$O1= “Scheduling RT”),OR($W1="Harvard pilgrim",$W1=”MEDICAID-Massachusetts”,$W1=”BMC / Northwood”,$W1= “Neighborhood Health Plan-CareCentrix **Sleep Only*”,$W1= “NETWORK HEALTH”, $W1=”TRICARE PRIME”, $W1=”TUFTS HMO”,$W1=”TUFTS HP MEDICARE PREFER”,$W1=”UNITED HEALTHCARE PA REQ'D OVER 1000”, $W1=”HEALTH PLANS INC”, $W1=”BCBS ANTHEM for ME NH CT Patients”),$Y1="")

This is the built formula, prompts an error. Cant figure out why.

 

[Andrew Poulsom]

Word has given you the wrong double quotes there. Try:

=AND(OR($O1="BR- PAP New Setup: Canton",$O1="BR- PAP New Setup: Kittery",$O1= "BR- PAP New Setup: Sandwich",$O1= "Scheduling RT"),OR($W1="Harvard pilgrim",$W1="MEDICAID-Massachusetts",$W1="BMC / Northwood",$W1= "Neighborhood Health Plan-CareCentrix **Sleep Only*",$W1= "NETWORK HEALTH", $W1="TRICARE PRIME", $W1="TUFTS HMO",$W1="TUFTS HP MEDICARE PREFER",$W1="UNITED HEALTHCARE PA REQ'D OVER 1000", $W1="HEALTH PLANS INC", $W1="BCBS ANTHEM for ME NH CT Patients"),$Y1="")

 

[ccheever2009]

That worked, thank you! much appreciated!