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

damon.tackett

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.

Monique Odom-Stearn

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.

damon.tackett

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

Eric Law

@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-Stearn

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.

damon.tackett

@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

@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

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-Stearn

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.

damon.tackett

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

ker9

@damon.tackett it acts like a line return

damon.tackett

@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

Hi @damon.tackett

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

damon.tackett

@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

@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

@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

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

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), "")