Automatically refresh Dependent Drop Down List

[CV12]

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!!

 

[GWteB]

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]

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]

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.

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]

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.

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]

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.

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]

You're welcome & thanks for the feedback