Show Col1 values as a list of values whose Col2 value is "Yes" in excel-CodePudding

My goal is to show Col1 values in a dropdown list if the respective rows value of Col2 is "Yes"

From my example, I want to show the values A ,C, D because the respective Col2 value equal is "yes" but do not want to show B and E

Currently showing as picture 1

but want to show like bellow picture

How can I do this using "Data Validation" or any other way?

Thanks for your help :)

CodePudding user response：

Office 365:

This doesn't seem to work as a formula for the DV List

=FILTER($A:$A,$B:$B="Yes")

However, put that formula somewhere on your worksheet where it won't be normally seen. Then refer to that range in the DV formula:

=FILTER function in Z1
DV formula:  =Z1#

CodePudding user response：

Might not be the optimal approach but you can definitely do it with VBA. Also works with older versions of Excel which might not have the Filter function.

Put this code into the Module of the worksheet where your data is. Then change one of the cells in your data to have the code run once, this will add the data Validation to the specified cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr() As Variant, targetRange As Range
    '"replace Range("dataa") with your range containing data (the letters and the Yes/No)"
    '"must have two columns with values for validation in first and Yes/No in second column"
    Set targetRange = Range("dataa")
    
    If Intersect(Target, targetRange) Is Nothing Then Exit Sub
    arr = targetRange
    
    Dim validationString As String
    For i = 1 To UBound(arr)
        Dim val As String, yesNo As String
        val = arr(i, 1)
        yesNo = arr(i, 2)
        If yesNo = "Yes" Then validationString = validationString & val & ","
    Next i
    
    If validationString = vbNullString Then validationString = "  "
    
    validationString = Left(validationString, Len(validationString) - 1)
    
    Dim validatedCell As Range
    '"replace Worksheets("Sheet2").Range("D1") with the cell where you want to place validation"
    '"does not have to be on the same sheet as your data"
    Set validatedCell = Range("D1")
    
    validatedCell.Validation.Delete
    
    validatedCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:=validationString
End Sub