Selected Answer
In the validation specs of B2 enter the formula below in place of the existing cell reference.
=INDEX(INDIRECT($A$2),,1)
This will apply a different list to the drop-down depending upon the choice in A2. Note that the ranges are named as listed in E2:E3, which is the validation source for A2 - all as existing in your workbook. You also already have an even procedure which will change the selection from the list.
In this procedure you might replace the Select statement with the line below.
Range("B2").Value = Range(Target.Value).Cells(1).Value
This code would just insert the first item from the selected list. If the list doesn't exist the code would crash. One way to avoid that would look like this:-
On Error Resume Next
Range("B2").Value = Range(Target.Value).Cells(1).Value
If Err Then Range("B2").ClearContents
Below is an implementation of that idea.
Option Explicit
Option Compare Text 'makes macro non-case-sensitive
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Address = "$A$2" Then
Application.EnableEvents = False
On Error Resume Next
Range("B2").Value = Range(Target.Value).Cells(1).Value
If Err Then Range("B2").ClearContents
Application.EnableEvents = True
End If
End If
End Sub
