Lookup cell and copy entire cell, not the value

[The Intern]

Hey everyone,

I have a very long list of names I should put into a dropdown list. In order to make it a bit clearer and shorter, my idea is that the user states the first character of the name and a dropdown list with only the names starting with this character will appear in a close-by cell.
So I have a list of letters and dropdown lists like that:
A I Dropdown list for names beginning with A
B I Dropdown list for names beginning with B
C I Dropdown list for names beginning with C
...
Is there any possibility to use some kind of vlookup macro that copies the entire cell( with dropdownlist) related to the stated character.

My macro starts like that:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("J7")) Is Nothing Then
   If Range("J7") = "" Then GoTo 123

????

End If
Range("D5").Select
123
End If
End Sub

Now I need to fill the blank in the middle :-D
Thanks for your help!!

The Intern

[JBeaucaire]

As long as you main list is a single column and it's sorted ascending, you can actually accomplish all this in a single cell. I have a technique for creating smaller sublists from a larger list, all operating in the same cell. The page here leads to three different examples of ways to use it...

Sub DV Lists from a Larger DV List

The SINGLE NAME LIST version might be the most directly applicable:

Single Name List (Sub DV list)

With this, you could have a drop down listing "A Names, B Names, C Names, etc.." and after you make a choice, you then reopen the SAME drop down and now the list names appears. Very fun.

[The Intern]

Hey Jerry Beaucaire,

thank you so much, it's working perfectly! I just had to use the first suggestion you stated, because some names are in Thai or start with a number.

Thanks again!!!