Changing Dropdown list based on inputs

[jtf2103]

Apologies for being a noob.

I need some help with changing a dropdown list based on input(s) from other cells.

I have 4 columns: A, B, C, and Size

A, B, and C are dropdown lists using Data Validation and can be Y, N, or blank
Size is a dropdown list using Data Validation and can be XS, S, M, L, XL, blank

I want the Size dropdown to change based on Y's in A, B, and C

So if A, B, or C = 'Y' the Size list no longer shows XS and only has S, M, L, XL, blank
If two of the 3, A and B or B and C or A and C = 'Y' the Size list no longer shows XS or S and is only M, L, XL, blank
And if all 3 = 'Y' the Size list no longer shows XS, S, M and only shows L, XL, blank

I've tried a bunch of variations of IF(OR) and IF(AND) but can't seem to make it work fully.

Any help would be huge.

Thanks

[XLent]

based on your rules...

one option: enter the following list into a vertical range, in order: XS, S, M, L, XL -- name this range something like _Sizes

in the Size DV cell, assumed to be D2 in the below example (with A2:C2 holding A, B & C results), enter the following formula:

D2 DV custom rule:

=OFFSET(_Sizes,COUNTIF($A2:$C2,"Y"),0,ROWS(_Sizes)-COUNTIF($A2:$C2,"Y"),1)

as you adjust A2:C2 so the options in D2 will adjust accordingly.

note: whilst OFFSET is Volatile it has no bearing here given DataValidation use case - i.e. will only be invoked when the DV cell is activated.

for more complex solutions you would want to research dependent DataValidation -- Debra Dalgleish's site will offer good examples of this (Contextures)

[jtf2103]

Thank you.

That formula worked for the 1 and 2 of them being Y's but not when all 3 are Y's; when all 3 are Y's it still shows the M-XL
Attachment 820979

[XLent]

that link isn't valid -- attached is a working illustration (which will only return L + XL for 3 Ys)

[jtf2103]

Not sure what I messed up the first time, like I said I'm a noob, but it works now.

Can't thank you enough!