Formula: Read Text Column to Fill Multi-Select Column

Jeremy O

Hi everyone,

What is the formula I can use in a Dropdown: Multi-Select column (Revision column below) to select options based on text that is typed in a Text column (Notes1).

For example, if Notes1@row contains "+ Change Design" and/or "Change Material" in the cell under that column, what is the formula for a cell in the Revision column to populate with the selections of "REV04 - Change Material or Build" and other selections?

Genevieve P.

Hi @Jeremy O

There's a few ways that you could set this up, however instead of a formula, I would actually suggest that you could use the new Change Cell Workflow to populate your multi-select column based on what this other column contains!

Take a look at this Help Article (Change the Value of a Cell in an Automated Workflow) and this announcement post on the feature and let me know if you would like to see screen captures to explain further.

Cheers,

Genevieve

Jeremy O

Hi @Genevieve P.

I read through the article and announcement about Change Cell Workflow. I have typed a workflow and nothing happened.

I'm open to see screen captures. But do you have any formulas that can help as well?

Genevieve P.

Hi @Jeremy O

You'll want to have a Condition Block that says the content should contain that value, versus being equal to the value, like so:

You will also want to make sure you're not Replacing values but adding them.

A formula will be quite long because you will need to check the cell for each possible text value and return something specific for each instance, but it's possible!

It would be built out like so:

=IF(CONTAINS("+ Change Design", [Notes 1]@row), "Change Material or Build" + CHAR(10))

Then you would need to write this out for each possibility and add + between the options:

=IF(CONTAINS("+ Value 1", [Notes 1]@row), "Value 1" + CHAR(10)) + IF(CONTAINS("Value 2", [Notes 1]@row), "Value 2" + CHAR(10)) + IF(CONTAINS("Value 3", [Notes 1]@row), "Value 3" + CHAR(10))

Does that make sense?

Cheers,

Genevieve

Jeremy O

Thank you for the walkthrough and screen captures. I followed them, but the automation still has not worked. Even when I change the Notes1 field to help trigger the workflow, nothing happens.

Even though I know the formula would be quite long, the formula did work.

Genevieve P.

Hi @Jeremy O

I'm glad to hear the formula version works for you! We can stick with this then instead of the workflow. (Note that the workflow above would only be triggered if you added a new row with the value in the cell from the start).

Do you need help building out the rest of the IF statement or did it make sense?

Jeremy O

Yes, I can continue with the formula. Thank you!

I figured the limitation of the workflow is it can only trigger after a change to a row or when a row is added.

Can I could use some help adding "OR" to the IF statement? Below, this is not working:

IF(OR(CONTAINS("+ CHANGE MATERIAL", [Notes 1]@row), (CONTAINS("+ CHANGE BUILD",[Notes 1@row), "REV04 - Change Material / Build" + CHAR(10)

Genevieve P.

No problem!

Yes, you should be able to add an OR statement. It looks like you just need to close off the OR before you go into what you want it to do, and take away the opening parentheses around the second CONTAINS.

Ex:

=IF(OR(CONTAINS(formula), CONTAINS(formula)), "Output")

Try this:

=IF(OR(CONTAINS("+ CHANGE MATERIAL", [Notes 1]@row), CONTAINS("+ CHANGE BUILD",[Notes 1@row)), "REV04 - Change Material / Build" + CHAR(10)

Cheers!

Genevieve

Nicole D

Hello - I'm using a similar formula but it's populating a "0" in front of the multiselect if the multiselect is zero or one value.

My formula:

=IF(CONTAINS("Yes", [Air Travel]@row), "Air/Flight" + CHAR(10)) + IF(CONTAINS("Yes", [Rental Car]@row), "Car" + CHAR(10)) + IF(CONTAINS("Yes", Hotel@row), "Hotel" + CHAR(10))

How can I have it remove the 0?

Genevieve P.

Hey @Nicole D

Try adding an instruction for what to do when the criteria is not met... you can say that it should equal "" or blank:

=IF(CONTAINS("Yes", [Air Travel]@row), "Air/Flight" + CHAR(10), "") + IF(CONTAINS("Yes", [Rental Car]@row), "Car" + CHAR(10), "") + IF(CONTAINS("Yes", Hotel@row), "Hotel" + CHAR(10), "")

Cheers,

Genevieve

Nicole D

That worked! Thank you @Genevieve P.

Danny Frank

Hello,

Trying to do the same thing as others, but I my logic is using a (not(isblank)) or <>"". Would like to take the text from Job1_Usked_ID (text/ number) and Job2_Usked_ID (text/number) and combine them into the Usked_IDs (dropdown multi select).

I have several similar columns that need to add numbers to the Usked_IDs column. (Up to 6). If the cell in one of the columns is blank, then the number would not be added.

Genevieve P.

Hi @Danny Frank

Try using the JOIN function in your instance:

=JOIN(COLLECT([Job1_Usked_ID]@row:[Job6_Usked_ID]@row, [Job1_Usked_ID]@row:[Job6_Usked_ID]@row, <> ""), " / ")

Cheers,

Genevieve