How to Hide the Selected Values in Data Validation Dropdown

What is the goal of your workflow? What problem does it solve, or how does it help you?
I figured out a way to have the Data Validation dropdown hide values that have already been selected, without putting the red error mark in the corner of the cell.

This could be useful, for example, on the Accounts sheet. Rather than having to check which accounts you’ve already selected, it could filter them automatically in the dropdown.

This is a tool for google sheets, not Tiller Money specific. But I figured it might be useful for those here who are developing templates. Maybe other people have already figured it out, but I couldn’t find it anywhere online so I decided to share it.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
The linked sheet below explains it, it’s not a complex sheet just a demo of how it works. I figured it would be easier to show rather than tell. There are no custom scripts or formulas.

Is it ok for others to copy, use, and modify your workflow?
Of course!

If you said yes above, please make a copy of your workflow and share the copy’s URL:
Data Validation - Hide Selected

This is pretty cool, @matt. I looked at it for a while thinking there must be a way to implement it that takes less real estate— that is the only downside— and I’m not sure there is. It is pretty clever. Well done!

1 Like

Thanks @randy! Yeah it’s not quite perfect, but honestly it’s better than I thought was possible in sheets.

Clever solution @matt.

You could use a formula like this. (Test it by putting it in D2):

This would generate a list of all the unused options in column D. And you could do a data validation range on that column.

This would eliminate all those hidden columns you have in E thru the end of the sheet.

The only problem is you would get the red error marks when options are selected.
But you wouldn’t be able to duplicate options or put in a value that’s not an existing option.


@jono thanks! I started with something like that actually, but I really wanted to avoid the error mark in the corner. I guess depending on what the priorities are either method could be useful.

1 Like