Formula: Read Text Column to Fill Multi-Select Column

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?


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin

    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?

  • 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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    Nicole D ✭✭✭✭

    That worked! Thank you @Genevieve P.

  • Danny Frank
    Danny Frank ✭✭
    edited 03/12/24

    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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!