ccheever2009

New Member
Joined
Jul 30, 2013
Messages
9
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

 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 30, 2013
Messages
9
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

New Member
Joined
Jul 30, 2013
Messages
9
=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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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="")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,413
Messages
5,571,965
Members
412,429
Latest member
brahmaiah
Top