Formula to pull selected text from a cell into a dropdown list.

Options

What I am needing to do is be able to pull specific pieces of information from Customer Order to either Austin# or Tulsa# those two columns are drop down lists with specific items. Austin# drop down list is two items BA1 and BA2. Tulsa# drop down list is BT1 and BT2. The Customer Order is a text field that can contain any information but will also include some combination of the items in the drop down list. So essentially what I am looking to do is a formula that if Customer Order contains BT1 it would select BT1 in the Tulsa, if it contains BT2 it would select BT2 or if it contains both it would select both. I was thinking using the drop down list in Austin# and Tulsa# columns would allow me to restrict what forms would be available in those columns. I have tried contains formulas but I can't seem to make it select in the drop down if there is more than one variable. I also tried creating an automation, but again can't make it reflect multiple variables.

I also tried removing the drop down list and just making Tusla# column a text column with these formulas but no success.

=IF(CONTAINS([Customer Order]@row, "BT1"), "BT1", " ") will work if there is only one variable.

=IF(CONTAINS([Customer Order]@row, "BT1"), "BT1", IF(CONTAINS([Customer Order]@row, "BT2"), "BT2", " ")) doesn't want to pull in both variables.


Best Answers

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @damon.tackett,

    Since you're working with multiple-select dropdowns, I would suggest using an automation. When you're working with a condition path in automations, the important thing to understand is that it will look for the first met condition and only move forward to the next condition path if the first one isn't met.

    I would make two automations, one for Austin and one for Tulsa. You can set it up like:

    Trigger: When rows are added or changed, when Customer Order changes to Any Value when triggered

    Conditions: Customer Order contains BA1 and where Customer Order contains BA2

    Change Cell Value: Austin# to BA1, BA2

    add condition path

    Condition: Customer Order contains BA1

    Change Cell Value: Austin# to BA1

    add condition path

    Condition: Customer Order contains BA2

    Change Cell Value: Austin# to BA2

    Then you can repeat the above for the Tulsa automation.

    This is telling the automation: Look to see if the customer order contains both. If yes, select both in Austin#. If not, look to see if it only contains BA1. If yes, select that. If not, look for BA2. If yes, select that.

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

  • damon.tackett
    damon.tackett ✭✭
    Answer ✓
    Options

    @ker9 interesting. what does the CHAR(10) represent? My drop downs list out the items individually.

«1

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    @damon.tackett You'll need an AND statement to make it work, also your CONTAINS is backwards.

    =IF(AND(CONTAINS("BTS1", [Customer Order]@row), CONTAINS("BTS2", [Customer Order]@row)), "BTS1 BTS2", IF(CONTAINS("BTS1", [Customer Order]@row), "BTS1", IF(CONTAINS("BTS2", [Customer Order]@row), "BTS2", "")))

    Just change BTS for BA for your other column.

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @damon.tackett,

    Since you're working with multiple-select dropdowns, I would suggest using an automation. When you're working with a condition path in automations, the important thing to understand is that it will look for the first met condition and only move forward to the next condition path if the first one isn't met.

    I would make two automations, one for Austin and one for Tulsa. You can set it up like:

    Trigger: When rows are added or changed, when Customer Order changes to Any Value when triggered

    Conditions: Customer Order contains BA1 and where Customer Order contains BA2

    Change Cell Value: Austin# to BA1, BA2

    add condition path

    Condition: Customer Order contains BA1

    Change Cell Value: Austin# to BA1

    add condition path

    Condition: Customer Order contains BA2

    Change Cell Value: Austin# to BA2

    Then you can repeat the above for the Tulsa automation.

    This is telling the automation: Look to see if the customer order contains both. If yes, select both in Austin#. If not, look to see if it only contains BA1. If yes, select that. If not, look for BA2. If yes, select that.

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

  • damon.tackett
    Options

    @Monique_Odom_Comcast I had looked at the automation previously, but didn't go in the correct order from what I see in your example. This totally makes sense. Question though. Does the order the items are listed impact the automation? For example, instead of listing the items BA1 BA2 if they listed them BA2 BA1 would the automation still pick them up? or BA1 BT1 BA2 BT2? Or do i have to account for those in additional conditions? I could see that being a long conditions flow in my real world application.

  • damon.tackett
    Options

    @Eric Law of course...the order is wrong...ughh...well that makes more sense now! Thank you!

    So same question I just asked Moique....Does the order the items are listed impact the formula? For example, instead of listing the items BA1 BA2 if they listed them BA2 BA1 would the formula still pick them up? or BA1 BT1 BA2 BT2? Or do i have to account for those in additional potenialities with more nested formulas? I could see that being a long nested formula flow in my real world application.

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 10/24/23
    Options

    Hi @damon.tackett & @Eric Law

    Eric's formula slightly updated where bolded:

    =IF(AND(CONTAINS("BT1", [Customer Order]@row), CONTAINS("BT2", [Customer Order]@row)), "BT1" + CHAR(10) + "BT2", IF(CONTAINS("BT1", [Customer Order]@row), "BT1", IF(CONTAINS("BT2", [Customer Order]@row), "BT2", "")))

    Depends on how your dropdown is setup.

    Order does not matter:


    Hope this helps!

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Options

    Hey @damon.tackett,

    For the automations, it does not matter in which order they are written in the Conditions box for when both are used. You will just want to make sure that the "both" instance is the first condition path used in the automation.

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

  • damon.tackett
    damon.tackett ✭✭
    Answer ✓
    Options

    @ker9 interesting. what does the CHAR(10) represent? My drop downs list out the items individually.

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    @damon.tackett it acts like a line return

  • damon.tackett
    damon.tackett ✭✭
    edited 10/30/23
    Options

    @ker9 I am really liking your solution, but I do have a question. If I try to expand it from the two options it doesn't seem to be pulling them in. I am sure it is something small I am missing. The second part of the formula seems to be working fine if there is only one selection entered on in the Customer Order column. Here is the expanded formula:

    =IF(AND(CONTAINS("BT1", [Customer Order]@row), CONTAINS("BT2", [Customer Order]@row), CONTAINS("BT3", [Customer Order]@row), CONTAINS("BT4", [Customer Order]@row)), "BT1" + CHAR(10) + "BT2" + CHAR(10) + "BT3" + CHAR(10) + "BT4", IF(CONTAINS("BT1", [OS98 Forms]@row), "BT1", IF(CONTAINS("BT2", [Customer Order]@row), "BT2", IF(CONTAINS("BT3", [Customer Order]@row), "BT3", IF(CONTAINS("BT4", [OS98 Forms]@row), "BT4", "")))))

    I should also note that the Customer Order field could contain any combination of the BT#s

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    Hi @damon.tackett

    In the 2nd and 5th IF statements you have a different column name [OS98 Forms], is that correct?


  • damon.tackett
    damon.tackett ✭✭
    edited 10/30/23
    Options

    @ker9 no. they should be the same as the others.

    It should read:

    =IF(AND(CONTAINS("BT1", [Customer Order]@row), CONTAINS("BT2", [Customer Order]@row), CONTAINS("BT3", [Customer Order]@row), CONTAINS("BT4", [Customer Order]@row)), "BT1" + CHAR(10) + "BT2" + CHAR(10) + "BT3" + CHAR(10) + "BT4", IF(CONTAINS("BT1", [Customer Order]@row), "BT1", IF(CONTAINS("BT2", [Customer Order]@row), "BT2", IF(CONTAINS("BT3", [Customer Order]@row), "BT3", IF(CONTAINS("BT4", [Customer Order]@row), "BT4", "")))))

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    @damon.tackett

    What isn't working? You may have to add another IF statement for BT1 BT2 BT3, and another for BT1, BT2.

  • damon.tackett
    Options

    @ker9 here is a screenshot of my sheet. Row one has no formulas but is an example of what should be being returned with the formulas. Row three has the formulas added to the BT and BA columns and what it is actually returning. I am basically just trying to extract the BT and BA parts into the appropriate column from a text field (Customer Order).

    Here is the formula I am using in BT Parts column. I have the same formula in BA Parts column but substituted BA for BT.

    =IF(AND(CONTAINS("BT1", [Customer Order]@row), CONTAINS("BT2", [Customer Order]@row), CONTAINS("BT3", [Customer Order]@row), CONTAINS("BT4", [Customer Order]@row)), "BT1" + CHAR(10) + "BT2" + CHAR(10) + "BT3" + CHAR(10) + "BT4", IF(CONTAINS("BT1", [Customer Order]@row), "BT1", IF(CONTAINS("BT2", [Customer Order]@row), "BT2", IF(CONTAINS("BT3", [Customer Order]@row), "BT3", IF(CONTAINS("BT4", [Customer Order]@row), "BT4", "")))))

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 10/31/23
    Options

    I think we might need bigger guns for this, @Paul Newcome

    Collect anything that contains "BT" and then drop it in the multi-select column?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Instead of using IF/AND to try to account for every possible variation, we can use a basic IF statement to check for one, use a basic IF statement to check for another and then "add" them together.


    Written for reading:

    =IF(CONTAINS("BT1", [Customer Order]@row), "BT1" + CHAR(10), "")

    +

    IF(CONTAINS("BT2", [Customer Order]@row), "BT2" + CHAR(10), "")


    Written for sheet:

    =IF(CONTAINS("BT1", [Customer Order]@row), "BT1" + CHAR(10), "") + IF(CONTAINS("BT2", [Customer Order]@row), "BT2" + CHAR(10), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!