Only Display YES/NO Drop Down if Adjacent Cell is Populated

[RaviWildcat]

Hi Everyone

My Data looks like this

See how in column B there is a list of cities and in Column C I have an adjacent cell with Drop Downs equal to YES/NO

The issue is, sometimes I have 4 cities and sometimes I have 2 cities. It looks a little silly to have drop downs next to blank cells

How can I setup my validations in Column C to only display (Yes/No) if the adjacent value in Column B is populated?

Thanks,
Ravi

A	B	C
2. YES
3. NO
4	City List	Check
5	Bayonne	No
6	Edison	No
7		Yes
8		Yes

 

[Yongle]

Are values in column B entered manually?
Code below sets up correct data validation when values in column B are amended by user
Place code in sheet window
(right click on sheet tab name \ select view code \ paste code into the open window)
Workbook should now be saved as macro enabled

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range, C As Range, notEmpty As Boolean
If Not Intersect(Target, Range("B5:B99999")) Is Nothing Then
    For Each Cel In Target
        notEmpty = (Len(Cel) > 0)
        Set C = Cel.Offset(, 1)
        Application.EnableEvents = False
        C.ClearContents
        Application.EnableEvents = True
        C.Validation.Delete
        If notEmpty Then C.Validation.Add Type:=xlValidateList, Formula1:="Yes,No"
    Next Cel
End If
End Sub

 

[RaviWildcat]

Thank you!

The values in column b are NOT entered manually

Ravi

 

[Yongle]

Assumes all the data is new data
Run after inserting data in sheet

Sub AddDataValidation()
    Dim Target As Range, Cel As Range, C As Range, notEmpty As Boolean
    Set Target = Range("B5", Range("B" & Rows.Count).End(xlUp))
    For Each Cel In Target
        notEmpty = (Len(Cel) > 0)
        Set C = Cel.Offset(, 1)
        Application.EnableEvents = False
        C.ClearContents
        Application.EnableEvents = True
        C.Validation.Delete
        If notEmpty Then C.Validation.Add Type:=xlValidateList, Formula1:="Yes,No"
    Next Cel
End Sub

 

[RaviWildcat]

Thank you! I will add this!

 

[Peter_SSs]

What about if you select C5:C? & set up your Data Validation like this?
The drop-down arrow would still appear if you select say C7 but it will be inoperable.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)