How to limit dropdown list values to only be selected once per sheet

J Markman

I am trying to figure out if it is possible to create a dropdown list and then enforce that each of the potential values could only be selected once per sheet.

For example:

I have a sheet with two columns. Project Name and Project Priority.

Project Priority is a dropdown list with preset values.

If I select the Project Priority as "2" for line one I do not want to be able to have any other lines chosen as "2" for that column.

Is this usage where each of the potential values in the dropdown list is unique and can only be selected once possible with the system?

Is this a feature request perhaps?

Sameer Karkhanis

As far as I know Smartsheets, this is not possible.

Mike TV

@J Markman

I can think of one method that would be kind of close. It would require the Data Shuttle app and the drop down choices would update every 15mins. If a duplicate could be chosen in less than 15mins, then this probably wouldn't work.

If this will work, first you'll want to set up a database sheet of drop down choices. You'll add a column to designate whether a drop down choice has been used yet or not. I'd suggest 2 options in this column: active or disabled. Then you write a formula to choose 'disabled' if it's been used on the other sheet, otherwise it's 'active'.

Then you create a Data Shuttle workflow to offload data from that drop down database sheet every 15mins. You create another Data Shuttle workflow to update drop down choices on attachment.

That should do it for keeping your drop down choices updated to what hasn't been chosen yet. However, like I said, it'll only update the drop down choices every 15mins. So if you need it updated immediately when a choice is selected then this probably won't work for your needs.

Andrée Starå

Hi @J Markman

I hope you're well and safe!

To add to Mike's excellent advice/answer.

• I recently developed something similar for a client using a Form with an advanced Form Logic structure.

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!