Search code examples
excel-formuladropdown

how to display a dropdown dynamically based on more than one possible value selected in a previous dropdown?

enter image description hereCurrently, this formula works in Excel after accessing Data Validation:

=IF(A2="A",YES_NO,NO_DATA)

In plain language, if the value in cell A2 = "A", then display a dropdown list in cell B2 with the values of YES or NO. Otherwise, don't do anything or NO_DATA.

What I would like to do is something like this:

=IF( (A2="A" Or A2="B" Or A2="C"),YES_NO,NO_DATA)

In other words, by choosing either A, B, or C, the YES_NO dropdown list should appear. Otherwise, NO_DATA.

I've tried a bunch of syntax efforts with no luck.

I tried using an IF statement within Data Validation. It seemed like my best option.

The screen shot is what I consistently get.

Solution

  • If you have two rows. One with YES and NO, and one with NO DATA, then you can refer to these lists with the Name Manager. This makes this much easier.

    Then you can use this formula in Data Validation:

    =IF(OR(A2="A";A2="B";A2="C");YES_NO;NO_DATA)

    In this case YES_NO is the name of the list containing "Yes" and "No", and NO_DATA is a list containing "No Data"

    enter image description here