Automatically refresh Dependent Drop Down List

CV12

Board Regular
Joined
Apr 6, 2020
Messages
79
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello.



I have a question about something I have tried in this video.
At 10:30 exactly, we can see that input 'World Domination' gives divisions 'World 1', 'World 2' and 'World 3' in the drop down list.
When he changes the department to Mining, 'World 3' stays in cell C2 until he changes the drop down list. Is it possible to do this automatically.
So, when he changes to department Mining, cell C2 should change to M1, M2 or M3 automatically.

Thanks in advance!!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
There's a distinction between the value in the cell on the one hand and the data validation drop down list used on this cell on the other hand.

When he changes the department to Mining, 'World 3' stays in cell C2 until he changes the drop down list.
The contents of the cell don't change, the drop down list however has been changed actually, but that's only visible when you click on the arrow button.

So, when he changes to department Mining, cell C2 should change to M1, M2 or M3 automatically.
What could logically be done is delete the cell contents. After all, what's the point of choosing any (random) value from the new validation list?
Unless you know what should be choosen beforehand, but then you don't need a validation drop down at all.
 

axelg

New Member
Joined
Jan 21, 2016
Messages
22
What could logically be done is delete the cell contents. After all, what's the point of choosing any (random) value from the new validation list?
Unless you know what should be choosen beforehand, but then you don't need a validation drop down at all.
I'm interested in this one. DO you have a solution here? Indeed, excel can't know what he should choose from the list. But if you change the "department" the dependant list may become empty (Or that's what I would like to have)
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
The only way to accomplish that would be the use of VBA code. By intercepting the worksheet change event we can check whether the primary cell changes. If so then the secondary cell can be cleared.
Code could look like this, to be pasted in de module of the worksheet to be affected.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    With Excel.Application
    If Not .Intersect(Target, Me.Range("A2")) Is Nothing Then  ' << A2 is primary cell
        .EnableEvents = False
        Me.Range("C2").Value = ""                              ' << C2 is secondary cell
        .EnableEvents = True
    End If
    End With
End Sub
 

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
584
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here's another way that gives you the first value of the validation range in C2. Assumes your layout is the same as in the video.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo GetOut
    Application.EnableEvents = False
    Dim rng As Range, choice As String, lc As Long
    lc = Cells.Find("*", , xlFormulas, , 2, 2).Column
    
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        Set rng = Range(Cells(5, 3), Cells(5, lc)).Resize(2)
        choice = Range("A2").Value
        Range("C2").Value = Application.WorksheetFunction.HLookup(choice, rng, 2, False)
    End If
    
Continue:
    Application.EnableEvents = True
    Exit Sub
GetOut:
        MsgBox Err.Description
        Resume Continue
End Sub
 

axelg

New Member
Joined
Jan 21, 2016
Messages
22
The only way to accomplish that would be the use of VBA code. By intercepting the worksheet change event we can check whether the primary cell changes. If so then the secondary cell can be cleared.
Code could look like this, to be pasted in de module of the worksheet to be affected.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    With Excel.Application
    If Not .Intersect(Target, Me.Range("A2")) Is Nothing Then  ' << A2 is primary cell
        .EnableEvents = False
        Me.Range("C2").Value = ""                              ' << C2 is secondary cell
        .EnableEvents = True
    End If
    End With
End Sub
Many thanks,
This hould work for me.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
You're welcome & thanks for the feedback (y)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,586
Messages
5,838,230
Members
430,535
Latest member
krazykram

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