Dynamic Data Validation
 

I found the example on Debra Dalgliesh's
site(http://www.contextures.on.ca/DataValListAddSort.zip) does exactly what I
am after... (see my edits to the code below)

.....but I want to do the same thing for TransfersList located in column C
with target colum 10

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("validation")
If Target.Column = 17 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Dx List"), Target.Value)
Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("D" & i).Value = Target.Value
ws.Range("DxList").Sort Key1:=ws.Range("D2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub

Dynamic Data Validation
 

There's another sample file that updates multiple source lists:

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

Under Data Validation, look for 'DV0021 - Update Multiple Validation Lists'

tmirelle wrote:
I found the example on Debra Dalgliesh's
site(http://www.contextures.on.ca/DataValListAddSort.zip) does exactly what I
am after... (see my edits to the code below)

....but I want to do the same thing for TransfersList located in column C
with target colum 10

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("validation")
If Target.Column = 17 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Dx List"), Target.Value)
Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("D" & i).Value = Target.Value
ws.Range("DxList").Sort Key1:=ws.Range("D2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html