function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MichaelEMichaelE 

Custom Formula Help

This is a formula that I have written for the US, but I need to expand to the UK, Germany and France....I hope someone can help me.

IF(LEN(Country=0, "None",

IF(CONTAINS("USA:United States", Country), "Office A",

IF(CONTAINS("UK:United Kingdom",Country), "Office B",

IF(CONTAINS("France), "Office C",

IF(CONTAINS("Germany), "Office D")))))

 

IN BETWEEN THESE TWO FORMULAS, I NEED SOME FORM OF LOGIC TO SAY, IF COUNTRY = Oficce A, Then divide the regions further by the following criteria....not sure how to do this.

 

 

IF(LEN( State)=0, "None", 

IF(CONTAINS("CA:NV:OR:WA:MT:ID:WY:CO:UT:AZ:NM:TX:HI:AK", State), "San Francisco", 

IF(CONTAINS("SC:NC:VA:WV:KY:OH:ND:SD:NE:KS:OK:LA:AR:MO:IA:MN:WI:IL:IN:MI:TN:AL:MS:GA:FL", State), "Chicago", 

IF(CONTAINS("PA:NY:VT:NH:ME:MA:RI:CT:NJ:DE:MD:D.C.", State), "New York","Other"))))

 

 

I would appreciate any help you can give me.

SAPOCSAPOC

You can use CASE instead of IF

 

Case(country,

"USA:United States","Office A",

"UK: United Kingdom","Office B",

"France","Office C",

"Germany","Office D",

"Unknown")

 

you may have to modify below logic.

Case(State,

"CA:NV:OR:WA:MT:ID:WY:CO:UT:AZ:NM:TX:H​I:AK", "San Francisco", 

"SC:NC:VA:WV:KY:OH:ND:SD:NE:KS:OK:LA:A​R:MO:IA:MN:WI:IL:IN:MI:TN:AL:MS:GA:FL", "Chicago",

"PA:NY:VT:NH:ME:MA:RI:CT:NJ:DE:MD:D.C.​", "New York",

"Unknown")

 

I hope this helps.