2

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 behaviour 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 dropdowns (as intended), however 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 invalidates it(good), but it does allow it to be selected again(bad)

Conclusion?

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 thought 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 dropdown (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.

ETA: sample sheet of my partial solution

  • 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
0

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.

1

  • 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
  • I've added a linked sheet with full edit rights to the OP showing my partial solution (which is similar to yours) – Void Singer Jan 24 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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