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

RaviWildcat

Board Regular
Joined
Jun 18, 2010
Messages
97
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



ABC
2. YES
3. NO
4City ListCheck
5BayonneNo
6EdisonNo
7Yes
8Yes
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Assumes all the data is new data
Run after inserting data in sheet
VBA Code:
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,548
Office Version
  1. 365
Platform
  1. Windows
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’)

1604813127901.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,994
Messages
5,639,436
Members
417,089
Latest member
jonstr101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top