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

SimonJackson123

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?

Paul Newcome

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.

Paul Newcome

It is not possible using the clear cell automation. You would need to set up another change cell automation that will output all of the values you want to keep based on the various conditions.

SimonJackson123

Thanks for your response Paul. So I would need to create an automation for every single possible outcome of the different Tasks? In the case above I have 4 tasks and two possible outcomes for each task, so 16 automations?

Paul Newcome

Do you need to be able to manually adjust the teams as well, or would an option be a formula?

SimonJackson123

Looking to have the teams auto-populated, I would rather not have to touch this cell manually. Is it possible to have multiple values attributed to a cell from a formula?

Paul Newcome

It may be possible depending on the exact logic you need and the expected output/use. Are these teams contacts in a contact type column, or is it just text that indicates which teams are being used?

SimonJackson123

It is just text that indicates the team/teams

Paul Newcome

Are you able to outline the logic for which teams go where and when?

SimonJackson123

I'm essentially looking to have anything that is completed to not show up in the Teams column. If a Task is not completed then I want the team to appear.

Something like the below:

The example provided is very much a simplified version of what I am trying to do, but the idea of it is the same. I tried building out a FOR loop but wasn't able to make the different teams appear as above.

Paul Newcome

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.

SimonJackson123

Excellent! That is a brilliant tip about adding different statements together, I did not know about that. I have been nesting IF statements for days until now. Also very insightful about CHAR(10). I did not know this either.

I can see that this is working for my test sheet here. I will try out this method on my actual data on Monday and let you know how I get on. Have a great weekend!

Paul Newcome

Happy to help. 👍️