Restrict duplicates in a dropdown single select column
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 :)
Answers
-
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
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hello - I'm still learning SS. How do I apply the conditional formatting to highlight a duplicate within a dropdown column?
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Update: I figured it out!
That does make sense! How do I set up the conditional formatting?
-
Have you added the helper column? Is it text, a checkbox, or something else?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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!
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!