Any way to separately Validate and create a Dropdowns in Google Sheets / Excel?

by Void Singer   Last Updated January 21, 2018 00:03 AM

Sample Scenario:

sheet 1 has a 3 Dropdowns from sheet 2
Sheet 2 has two lists, {"A";"B"}, and a second dynamic list that copies the first IF the value doesn't already appear on sheet 1

The behavior I'm looking for is to validate on list one, but provide the drop down from list 2.

Current Problem:

if a user selects "A" from the dropdown on sheet 1 (pulling from the dynamic second list on sheet 2), it will no longer show up as a selectable option in the further drop downs (as intended), however the the first drop down now shows as invalid (because that item is no longer on the dynamic second list)

Alternately, if I use the first list, selecting an item no longer invavlidates it(good), but it does allow it to be selected again(bad)


As far as I can tell there is no way to provide a dropdown list separately from validation, and no way to apply multiple validations on the same cell. I though perhaps there might be some way for a custom formula to provide separate lists for the dropdown and for the validation, but if there is I'm not finding it.

The closest I've come is creating a separate dynamic list for every drop down (dependant on those above it), but that would be massive considering the actual size of the dataset, dropdowns, actual dynamic filter, and would bog down the app window... I really need a better workaround and I'm hoping one of you kind folk has it

Related Questions

Periodic data query for updated lists on a website

Updated October 22, 2015 05:01 AM

Google Sheets Dropdown using Multiple Sets of Data

Updated November 29, 2017 13:03 PM

Limit values to other ranges

Updated November 30, 2017 13:03 PM