Smartsheet Automation to Remove a Single Value from Multi-Select Cell


I have multiple columns that denote tasks; Task 1, Task 2, Task 3, Task 4. Each of these tasks can have a cell value of; Completed, In Progress, and Not Started. I then have a multi-value dropdown column called Team that denotes what teams are necessary to complete a project. I have an automation in place to add the teams to this column depending on the cell value for each task. For example, if Task 1 is either Not Started or In Progress then Team 1 should appear in the Team column. If Task 2 is either Not Started or In Progress then Team 2 should appear in the Team column etc. Multiple teams can appear in the Team column. Whichever tasks are not Completed should have the corresponding teams in the Team column. This is all working fine...

My question comes when I wish to remove a team from the Team column when a task becomes Completed. For example, if Task 1 becomes Completed, remove Team 1 from Team column, but keep all other teams that have tasks either Not Started or In Progress. Is it possible to remove a single value from a multi-select column without making the entire cell blank?


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ah. Ok. I had a very different idea of the data structure, so the screenshot definitely helps. Try this:

    =IF([Task 1]@row <> "Completed", "Team 1" + CHAR(10), "") + IF([Task 2]@row <> "Completed", "Team 2" + CHAR(10), "") + IF([Task 3]@row <> "Completed", "Team 3" + CHAR(10), "") + IF([Task 4]@row <> "Completed", "Team 4", "")

    The difference above as compared to typical IF statements is that usually they are nested, but in this case we want to "add" them together.

    NOTE: CHAR(10) is the built in delimiter for multi-select columns and will allow each team to be treated as an individual selection within the cell in the event you need to filter or report on it.