Restrict duplicates in a dropdown single select column

sazap

Hello all!

Does anybody know if SS has a way to restrict duplicates in a dropdown single select column? For example, "Project 4" is already using "D," so when I select a value for "Project 5," "D" would not be available since it's already in use.

Or, if I select "D" for "Project 5" it would automatically change "Project 4" to "E".

Thank you in advance :)

Brent Wilson

Smartsheet cannot do dynamic dropdowns..

But what you could do is create a helper column and have it count the instances in the column

=COUNTIF([Column 2]:[Column 2], [Column 2]@row)

This would then show a count of 2 if you select D.

You could then use conditional formatting to highlight the duplicate

You could also have a workflow rule that if Column 3 is greater than 1 then send an email to the sheet owner to say "duplicate assignment in project sheet"

I know this is not what you are looking for but might be a workaround until they develop this type of functionality

sazap

Hi Brent,

Thank you so much for the solutions, I think this is a good workaround. I hope we can see something like this coming up to Smartsheet capabilities.

Thanks,

Samara

Andrée Starå

Hi @sazap

I hope you're well and safe!

To add to Brent's excellent advice/answer.

Another option could be to use a form and the Form Logic feature to make it impossible to select something already selected.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

sazap

Hi @Andrée Starå

Yes, well and safe, and I hope you are too.

I appreciate the insight; it looks like a good additional solution; I will try both to see what works best in the sheet I'm working on.

Thanks,

Samara

Andrée Starå

@sazap

Excellent!

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Kacy

Hello - I'm still learning SS. How do I apply the conditional formatting to highlight a duplicate within a dropdown column?

Andrée Starå

Hi @Kacy

I hope you're well and safe!

You'd first need to add a so-called helper column with a formula that checks for duplicates, and then you'd reference it in the Conditional Formatting rule.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kacy

Update: I figured it out!

That does make sense! How do I set up the conditional formatting?

Andrée Starå

@Kacy

Have you added the helper column? Is it text, a checkbox, or something else?

Kacy

@Andrée Starå I added the helper column and then formatted it to highlight the column I'm looking for duplicates in. All looks good...thanks for your help!

Andrée Starå

@Kacy

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!