Data Vlaidation based on a formula

[James8761]

Hi,

I have created a simle Data Validation List, based on whether a city says London, New York or Venice.

I would like to create a formula that says if Cell C3 says New York, the Data Validation List in Cell C5 will only let you select an item from Cells D9:D11, If Cell C3 says London C5 will only let you select an item from Cells C9:C13, If Cell C3 says Venice C5 will only let you select an item from Cells E9:E11.

Is this possible at all please?

 

[mumps]

Try:

Sub CreateDVlist()
    Application.ScreenUpdating = False
    Dim fnd As Range, arr As Variant
    Dim LastRow As Long
    Set fnd = Rows(8).Find(Range("C3").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        LastRow = Columns(fnd.Column).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        arr = Application.Transpose(Range(Cells(fnd.Row + 1, fnd.Column), Cells(LastRow, fnd.Column)))
        With Range("C5").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Join(arr, ",")
        End With
    End If
End Sub