OK, hope you can follow. This assumes you have 2 sheets. First Sheet called "Sheet1" which contains a dropdown with all unique company Names. This code will update the named ranges dynamically, so if you add a new company, it will be visible in the Company Name dropdown immediately
Sheet1 looks like this: Cells A2 and B2 are Dropdown Boxes. The Data validation in A2 needs to be set to '_Companies' and for B2 to '_ContNums'
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Company Name[/TD]
[TD="align: center"]Contract Numbers[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Company1[/TD]
[TD="align: center"]12345[/TD]
[/TR]
</tbody>[/TABLE]
Right click the Sheet1 tab name and select 'View Code
Paste this code in tot he window that appears:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Address
Case Range("A2").Address ' User has changed Company Dropdown cell
GetContractNumbers
End Select
End Sub
Private Sub GetContractNumbers()
'This macro gets all contract numbers that are associated with the company selected in the dropdown box
On Error GoTo errHandle
Dim sCompany As String
Dim i As Integer 'Company loop integer
Dim j As Integer 'Contract Number Loop for Row F
i = 0
j = 1
Application.EnableEvents = False
Application.ScreenUpdating = False
sCompany = Range("A2").Value 'the company the user has selected
Sheets("Data").Range("F:F").Clear 'empty Column F so we can store the list
Do Until Sheets("Data").Range("C2").Offset(i).Value = "" ' Loop through all Company and contracts and get the list of contract numbers
If sCompany = Sheets("Data").Range("C2").Offset(i).Value Then 'We have a match
'Set next cell in row F to equal the contract number in the cell next to the name
Sheets("Data").Range("F" & j).Value = Sheets("Data").Range("C2").Offset(i, 1).Value
j = j + 1 'next row number in column F
End If
i = i + 1
Loop
Sheets("Data").Range("F1:F" & j - 1).Name = "_ContNums" 'Set the name o fthe range to '_ContNums' (contract Numbers)
If Sheets("Data").Range("_ContNums").Cells.Count > 0 Then Range("B2") = Sheets("Data").Range("_ContNums").Cells(1, 1)
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
errHandle:
MsgBox Err.Description
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
You then have a sheet named "Data" which has a table in column A of just unique company names. And a table in Columns C & D which associates contract numbers with companies. (Column B is left blank). It should look like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Company Names[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company Name[/TD]
[TD="align: center"]Contract Number[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Company1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company1[/TD]
[TD="align: center"]12345[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Company2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company2[/TD]
[TD="align: center"]12346[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Company3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company3[/TD]
[TD="align: center"]12347[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company1[/TD]
[TD="align: center"]12348[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Company2[/TD]
[TD="align: center"]12349[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]
Company3
[/TD]
[TD="align: center"]12350[/TD]
[/TR]
</tbody>[/TABLE]
Again right click the Tab name 'Data' and select 'View Code'
Paste this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub ' if more than 1 cell has changed, ignore
Select Case Target.Column
Case 1 'Column A has changed
SetCompanies
End Select
End Sub
Private Sub SetCompanies()
'This sub finds all cells with a name in them and generates the named range '_Companies' for use in the validation of the dropdown on Sheet1
Dim iFirstRow As Integer
Dim iLastRow As Integer
iFirstRow = 2 'Row number of first company
iLastRow = Range("A" & Rows.Count).End(xlUp).Row'last used row on column A
Range("A" & iFirstRow & ":A" & iLastRow).Name = "_Companies" 'name the complete list of companies to reference for the Companies Dropdown
End Sub
Now when you change the drop down in A2 on Sheet1 the dropdown in B2 should fill with only the numbers for that company.
This is all dynamic. If you add another company it will appear.
Point of note: you may need to set up both sheets and paste the code
BEFORE setting the validation on the drop downs. I'm assuming you understand about dropdown validation as you already use it.
Let me know if it's confusing.