[Google Sheets] Make items selectable by only 1 dropdown list at a time
March 18, 2020 4:14 PM   Subscribe

I have a list of items (from a range, if it matters), and I want those items available to all dropdown lists, but each item only selectable by one dropdown list at a time. So if I select one item in any dropdown list, now it's no longer available in any other dropdown list (on a first-come first-served basis of availability).

If this spreadsheet was a daily planner/schedule, say for a Monday, I'd have a dozen or so dropdown lists, and each dropdown list has dozens of times available such as 1:00 PM, 1:30 PM, 2:00 PM, etc. If I select 1:00 PM from the first dropdown, now it should not be available in any other dropdown list.

I understand there are better ways to format this spreadsheet than what I described but (I think) the dropdown lists are essential for my purpose. However I'm open to any/all ideas. Much appreciated.

(Right now I have a convoluted way of achieving this with an ARRAYFORMULA, which returns an error every single time. It all somehow works despite the error, but now my goal is to do this without errors)
posted by 1,000,000 to Computers & Internet (1 answer total) 1 user marked this as a favorite
I just solved this like this:

In a sheet, I have a list of cells that will contain my drop-downs, say A1:A6

In column C, I have my original list of values (so C1 = 1:00PM, C2 = 1:30PM, C3 = 2:00PM, whatever)

In column D I made a countifs() of the value in C, count how many times it appears in the selection of Dropdown Lists, so D1 is =countifs($A$1:$A$6,C1) - this will tell me how many times the value has been selected in the drop-downs

In column E I have an if statement that shows the value in column C if the count is 0, otherwise it's blank, so E1 is = if(D1=0,C1,"")

Now, when I set up my data validator, I select the list of values in column E. The drop-down won't show blanks, so as you select something and its "count" ticks to 1, it will now actually be blank and won't show up in your drop-down lists.

Did that make sense?
posted by brainmouse at 5:16 PM on March 18, 2020

« Older Another should I stay or should I go question with...   |   How to interpret my friend's analysis of systemic... Newer »
This thread is closed to new comments.