I've written an advanced formula, with LOTS of trial and error, for assigning leads to particular individuals who are sharing territories. It's based on the state field and the two letter abbreviation for states. It works great. But I also need to assign some leads from Central and South America to one of the individuals, so I added an OR to the formula as a test. It's not passing the syntax check. Can you see what is wrong here? I inserted the symbol for 'OR' and the syntax error message indicates that it expected boolean and received text. I'm too new to this to understand how to fix this. Thanks in advance! Robin
IF(ISBLANK(State),"None",
IF(CONTAINS("AK:Alaska:AZ:Arizona:CA:California:HI:Hawaii:ID:Idaho:NV:Nevada:MT:Montana:OR:Oregon:UT:Utah:WA:Washington:AB:Alberta:BC:British Columbia:NT:Nunavut:YT:Yukon Territory:MB:Manitoba:NU:SK:Saskatchewan:ON:Ontario", State), "Rachel",
IF(CONTAINS("AL:Alabama:AR:Arkansas:CO:Colorado:IA:Iowa:KS:Kansas:KY:Kentucky:LA:Louisiana:MN:Minnesota:MS:Mississippi:MO:Missouri:NE:Nebraska:NM:New Mexico:ND:North Dakota:OK:Oklahoma:SD:South Dakota:TN:Tennessee:TX:Texas:WY:Wyoming", State), "Rachel",
IF(CONTAINS("CT:Connecticut:DE:Delaware:DC:District of Columbia:Washington DC:FL:Florida:GA:Georgia:ME:Maine:MD:Maryland:MA:Massachussets:NH:New Hampshire:NJ:New Jersey:NY:New York:NC:North Carolina:PA:Pennsylvania:RI:Rhode Island:SC:South Carolina:VT:Vermont", State), "Wendy",
IF(CONTAINS("VA:Virginia:WV:West Virginia:IL:Illinois:IN:Indiana:MI:Michigan:OH:Ohio:WI:Wisconsin:NB:New Brunswick:NL:Newfoundland:NS:Nova Scotia:PE:Prince Edward Island:QC:Quebec", State), "Wendy", "Other"))))) ||
IF(ISBLANK (Country),"None",
If (CONTAINS ("CO:Colombia:SV:El Salvador",Country),"Rachel","Other"))
11 answers
Instead of using a Nested IF with a CONTAINS function why don't you try using a CASE function like this: CASE(State,
"AK","Rachel",
"Alaska","Rachel",
"CT", "Wendy",
"Connecticut", "Wendy",
CASE(Country,
"CO","Rachel",
"Colombia","Rachel",
"Other"))
