Dynamic drop down list, how to do?

[aishnad]

Sheet 1
ColA ColB
1 AnsA
3 AnsR
1 AnsF
1 AnsS
2 AnsD

Sheet 2 - Should have drop down
ColC - should have unique list from Sheet1!ColA. for eg. 1,3,2
ColD - list should be based on selection in ColC. For eg., if I select 1 in ColC, This should list AnsA, AnsF, AnsS

How to do this?

 

[jim may]

http://www.contextures.com/xlDataVal02.html

 

[nightcrawler23]

If that doesnt work for you....Try below code that gives you a unique list of items from ColA. change accordingly to print in sheet2. now it takes data from colA of activesheet and prints in the activesheet in colmunC.

Sub uniqueList()
Dim uniqueArray() As Variant
Dim count As Integer
Dim notUnique As Boolean

ReDim uniqueArray(0) As Variant
uniqueArray(0) = Range("A1")
count = 0

Dim cl As Range
For Each cl In Range("A2:A" & Range("A" & Rows.count).End(xlUp).Row)
    notUnique = False
    For i = LBound(uniqueArray) To UBound(uniqueArray)
        If (cl.Value = uniqueArray(i)) Then
            notUnique = True
            Exit For
        End If
    Next i
    
    If notUnique = False Then
        count = count + 1
        ReDim Preserve uniqueArray(count) As Variant
        uniqueArray(UBound(uniqueArray)) = cl.Value
    End If
Next cl

    For i = LBound(uniqueArray) To UBound(uniqueArray)
        Range("C1").Offset(i, 0) = uniqueArray(i)
    Next i
End Sub