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!
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!
You could use a formula like this. (Test it by putting it in D2): =FILTER(B2:B,ISNA(MATCH(C2:C,B2:B,0)))
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.
I’ve found this in my searches trying to create a dropdown list in the way you’ve suggested, but with numbers rather than text options.
Is there a way to do this, where the same number can appear twice? I’m in the early learning stages with these formulas and I think the ‘unique’ part of the formula is what’s stopping the same number appearing.
An example of use:
6 random numbers are generated. Some can be the same number
The numbers appear in 6 dropdowns, where selected values need to be hidden
I know it’s an old post but this has been the most helpful post I’ve found so far in my searches. Any help you can provide would be amazing.
I can’t immediately think of any way to do what you’re asking. My whole setup is based on a lookup that has no way of knowing how to count duplicated values. I’ll let you know if I think of anything though, sorry I can’t be more help!