Dependent drop down list in excel auto update

Question

I can create simple dependent drop down list in excel as shown in the below screenshot.

http://s14.postimg.org/sip3g4nc1/dropdown1.jpg If I select TeamA in the cell E1, all the three members of TeamA is shown in the drop down list at cell F1

Now I want to achieve the following scenarios -

1. The first member will be shown in F1 immediately selecting Team in E1. Currently whatever I select at cell E1, F1 stays unchanged until I change the value by clicking F1's drop down list.

2. There will be another two cells to hold members in G1 and H1. So, there will be three cells for three members. When I select Team in E1, immediately three cells (F1, G1 & H1) will be updated with the corresponding members. Each member cell will have drop down list having all the members to select desired member later.
   http://s16.postimg.org/zb2pja4hh/dropdown2.jpg

Answer 1

Assuming you used indirect referencing (the rows under TeamA are named TeamA and so on):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng(1) As Range, rng1 As Range
Set rng(0) = Range("E1") 'your primary selection
Set rng(1) = Range("F1:H1") 'your secondary selection range
Application.EnableEvents = False
If Not Intersect(Target, rng(0)) Is Nothing Then 'if you have changed your primary selection
    For Each rng1 In rng(1) 'each cell in your secondary selection
        i = i + 1
        rng1 = Range("" & rng(0).Value2)(i, 1) 'gets changed to the nth value in the indirect reference of the primary selection ("TeamA" 's second row is "MemberA2" for example)
    Next
End If
Application.EnableEvents = True
End Sub

You need to place this sub into your worksheet module and save the file with .xlsm extension.