If, Then formula for drop down columns

Options
Patrick_CFA
Patrick_CFA ✭✭
edited 02/28/23 in Formulas and Functions

I am trying to create an IF/THEN formula that automatically changes the value of fields in one column drop down if other values are selected in a different column.

Ex. =IF([Sub-Step]@row = "Research", [Support Stage]@row = "Discover")

First row --> what I'd like to see happen (manually typed in)

Second row --> tried using the formula above, but not generating


Parent/Child relationship

Support Stage with Sub-Steps listed for each stage.


If Sub-Step selected equals Research or Support Requested --> Support Stage = Discover

If Sub-Step selected equals Scope the Need, Conduct Assessment, Draft Proposal --> Support Stage = Plan

If Sub-Step selected equals Prepare, Train, Facilitate, Advise and Coach --> Support Stage = Execute

Best Answer

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Answer ✓
    Options

    @Patrick_CFA

    Try the formula below. It assumes that the only entries in Sub-Step are those you've listed. If there are blanks or other values that you want to ignore, then use the second formula.

    =IF(OR([Sub-Step]@row = "Research", [Sub-Step]@row = "Support Requested"), "Discover", IF(OR([Sub-Step]@row = "Scope the Need", [Sub-Step]@row = "Conduct Assessment", [Sub-Step]@row = "Draft Proposal"), "Plan", "Execute"))

    =IF(OR([Sub-Step]@row = "Research", [Sub-Step]@row = "Support Requested"), "Discover", IF(OR([Sub-Step]@row = "Scope the Need", [Sub-Step]@row = "Conduct Assessment", [Sub-Step]@row = "Draft Proposal"), "Plan", IF(OR([Sub-Step]@row = "Prepare", [Sub-Step]@row = "Train", [Sub-Step]@row = "Facilitate", [Sub-Step]@row = "Advise", [Sub-Step]@row = "Coach"), "Execute", "")))

    Hope this helps!

Answers

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Answer ✓
    Options

    @Patrick_CFA

    Try the formula below. It assumes that the only entries in Sub-Step are those you've listed. If there are blanks or other values that you want to ignore, then use the second formula.

    =IF(OR([Sub-Step]@row = "Research", [Sub-Step]@row = "Support Requested"), "Discover", IF(OR([Sub-Step]@row = "Scope the Need", [Sub-Step]@row = "Conduct Assessment", [Sub-Step]@row = "Draft Proposal"), "Plan", "Execute"))

    =IF(OR([Sub-Step]@row = "Research", [Sub-Step]@row = "Support Requested"), "Discover", IF(OR([Sub-Step]@row = "Scope the Need", [Sub-Step]@row = "Conduct Assessment", [Sub-Step]@row = "Draft Proposal"), "Plan", IF(OR([Sub-Step]@row = "Prepare", [Sub-Step]@row = "Train", [Sub-Step]@row = "Facilitate", [Sub-Step]@row = "Advise", [Sub-Step]@row = "Coach"), "Execute", "")))

    Hope this helps!

  • Patrick_CFA
    Options

    @Kelly P. - The second option worked great. Thank you!

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!