Excel - dependent dropdowns using indirect function

Question

I have created dependent dropdown using indirect function and data validation using which i have to create dynamic charts.

Whenever i am selecting an option in the first dropdown, the values gets updated accordingly but i need to click on the second dropdown to view the related options. For example: The first dropdown contains fruits, vegetables and colors. I have selected fruits first and the options are banana, apple, mango. I select mango in the second dropdown. Now, when i am selecting colors in the first dropdown, the second dropdown still shows mango, until i click on it and see various 'colors'(red, yellow, green) option.

Is there a way so that the first option in colors(red) appears in the second dropdown as soon as i select colors in the first dropdown?

I am using indirect of the cell link for dependent dropdowns.

Please suggest answers without VBA

Kresimir L. Kresimir L. · Accepted Answer · 2017-09-25T10:32:18

I don't think you can achieve this W/O VBA help. If you wish to consider VBA approach, then please paste this code in you Worksheet module of sheet where you have data validation.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
        Dim x As String, y As String
        x = Range("A1").Value
        y = Application.WorksheetFunction.VLookup(x, Range("A11:B13"), 2, False)
        Range("B1").Value = y
    End If
End Sub

This assumes that you have your data validations in Cells A1 and B1. you will need to add one simple lookup table (A11:B13 in my example) where you will put name of category in A column and related first value in B. column.

Worksheet_Change event will trigger only when cell A1 change its value.

Excel - dependent dropdowns using indirect function

1 Answers