Data Validation

[snash]

I've got a drop down list of customer names in C2. Now I'm thinking of adding a new region field in my file in C1. So if I do a validation of regions in C1, how would I get C2 to show only the list of customers associated to that region?

thanks in advance,

 

[snash]

Does anyone know if I need to have a code for this or if it can be done via formula?

 

[Fluff]

Have a look at this https://www.contextures.com/xlDataVal02.html

 

[Puertorekinsam]

Where does your Source data live?
You can do it with formulas... but it takes a lot of extra stuff to make it work.... ranks and counts, and IfBlanks... You "CAN" do it, but I would recommend using two active control comboboxes instead.

Sub GetUniqueAndCount()

    Dim d As Object
    Dim tmp As String
    Dim i As Integer
    Set d = CreateObject("scripting.dictionary")
    i = 1
    Do Until Cells(i, 1) = ""
        tmp = Trim(Cells(i, 1))
        If Len(tmp) > 0 Then d(tmp) = d(tmp) + 1
        i = i + 1
    Loop
    Me.ComboBox21.Clear

    For Each k In d.keys
        Me.ComboBox21.AddItem k
    Next k

End Sub

Private Sub ComboBox21_Change()
Me.ComboBox22.Clear

i = 1

Do Until Cells(i, 1) = ""
    If Cells(i, 1).Value = Me.ComboBox21.Value Then
    Me.ComboBox22.AddItem Cells(i, 2)

    End If
        i = i + 1
Loop
End Sub

 

[snash]

thanks, Can I just copy this code or do I need to do other things first? I will have about 5 regions in one drop down and then in another drop down there could be 20 customers associated to the relevant region

 

[Puertorekinsam]

Where do the regions and customers exist in your workbook? Do you have a separate tab that has the master list?


You will need other things... For example, I don't actually drop the value of the drop down into the cells where your old drop downs were.