Dynamic Data Validation

Hi,

I have a question on the above but can't seem to find a solution.

There are two ways that I can find for dealing with dynamic lists via data validation:-

1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection

Is there any way to get around 1 without having to do 2?

E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?

Thanks
Matt

Matt, can you post an example - I'm struggling to visualise... you're saying something like you have your lists in B1:C3 as follows (colon represents column delimiter)

a:s
b:t
a:u

User then selects from a list containing "a,b" and assuming "a" is selected the second list should thus contain "s,u", is that correct ?

A very basic example could be...

A2: data validation list of choices a,b
A3: 2nd validation list - dynamic content to be based on selection in A2

B2:C4 as previously outlined, ie:
a:s
b:t
a:u

In D2:D4 will be the final list pending selection in A2

D2: =INDEX($C$2:$C$4,MATCH(1,INDEX(($B$2:$B$4=$A$2)*ISNA(MATCH($C$2:$C$4,$D$1:$D1,0)),0),0))
copied down to D4

Create a new Name

Name: =_finallist
RefersTo: =$D$2:INDEX($D$2:$D$4,MATCH(REPT("Z",255),$D$2:$D$4))

Now set up Data Validation for A3

List -> Source: =_finallist

Is that along the right lines ?

Re: VBA... based on my example you could possibly use a Change event on the cell containing the Validation list such that as it changes the 2nd Validation list is purged and repopulated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, strList As String
If Target.Address = "$A$2" Then
    With Range("A3").Validation
        .Delete
        For Each rngCell In Range(Cells(2, "C"), Cells(Rows.Count, "C").End(xlUp)).Cells
            If UCase(rngCell.Offset(, -1).Value) = UCase(Target.Value) Then strList = strList & "," & rngCell.Value
        Next rngCell
        .Add xlValidateList, , xlOr, Formula1:=Replace(strList, ",", "", 1, 1)
    End With
End If
End Sub