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.

  • Is this question about Google Sheets or about Excel? They have some compatible features but other aren't. The same for Excel "installable" Vs Excel Online. – Rubén Jan 20 '18 at 23:54
  • Primarily Sheets, the extra tag is to pull attention from excel experts (which there are more of) that may have a compatible solution – Void Singer Jan 21 '18 at 0:15

the only way how one cell can have more than one data validation is to use proxy data validation from another cell on that cell. let's say cell A1 has set data validation from a list of items and those items are 1,2,3,4. then you can set proxy data validation from (on) B1 cell with a custom formula validation like =ISODD(A1) creating double data validation for cell A1.


  • It's useful information, but it doesn't get any closer to solving the problem stated (which is a combination of 'use once only' and 'A not B' problems). To be fair, as far as I can tell it's just not something that can be done on a general level without a script to calculate each dropdown independently.. otherwise it takes a separate dynamic list per dropdown. – Void Singer Jan 21 at 3:41
  • @VoidSinger can you provide sheet with stated problematics and generic examples of expected outcomes? – MARK MY ANSWER 2 days ago

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.