Need help with IF formula

Options
DominicR
DominicR ✭✭
edited 12/09/19 in Smartsheet Basics

Hi All,

I've looked everywhere with a similar issue but nothing comes up.

I'm trying to create a column where the cells auto-populate a facility name depending on the territory selected in the territory column. I'm assuming I need to use =IF but tried various ways with no luck! I've included a screenshot below to hopefully help.

E.g. The facility for Colombia should be DLA where as the facility for UK should be DTU.

Thanks for your help!

Screenshot.png

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Dominic,

    Try something like this.

    You'd add to the pattern for more conditions. How many different territories do you have?

    =IF(TERRITORY@row = "Colombia"; "DLA"; IF(TERRITORY@row = "UK"; "DTU"))

    The same version but with the below changes for your and others convenience.

    =IF(TERRITORY@row = "Colombia", "DLA", IF(TERRITORY@row = "UK", "DTU"))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Would that work?

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • DominicR
    Options

    Ah amazing, this is working! Now the only issue is is adding multiple territories (we have 40+ territories) which I've tried by exchanging the comma to a period or semi-colon but neither are working.

    How would I go about adding multiple territory options? (e.g. Taiwan / Indonesia / India should be for DLA facility and UK / France / Norway should be for DTU facility). FYI, each row can only have one territory selected from a drop-down box.

    Thanks for your help, really appreciate it!

    Dominic

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Excellent!

    Happy to help!

    Try something like this for adding alternatives. 

    We could also create a VLOOKUP or INDEX/MATCH solution that would look up the correct value in a table instead.

    =IF(OR(TERRITORY@row = "Taiwan"; TERRITORY@row = "Indonesia"; TERRITORY@row = "India"); "DLA"; IF(OR(TERRITORY@row = "UK"; TERRITORY@row = "France"; TERRITORY@row = "Norway"); "DTU"; ""))

    The same version but with the below changes for your and others convenience.

    =IF(OR(TERRITORY@row = "Taiwan", TERRITORY@row = "Indonesia", TERRITORY@row = "India"), "DLA", IF(OR(TERRITORY@row = "UK", TERRITORY@row = "France", TERRITORY@row = "Norway"), "DTU", ""))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • DominicR
    Options

    This worked perfectly, I'm all good now! Thanks for your help!!!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.