Conditional Drop Down lists - with dynamically changing values

[papplaszlodaniel]

Hi dear all,

I face a challenge what I don't seem to be able to pass.

I have a separate Excel (let's call it Workbook_1) with the details of contracts with different companies. One company can have multiple contracts.
In a new Excel (let's call it Workbook_2), I would need two columns with drop down lists, but the second should depend on the first.

E.g. in Workbook_2:

Sheet1: contains the data (transferred from Workbook_1 with PIVOT)
​

Sheet2:​

• 
  [*=1]Column A - I can pick a company's name (let's say Company_X) from a drop down list. (this part is easy-peasy)
  [*=1]Column B - I should see now only this chosen company's contracts' numbers in a drop down list.

I found that trick when you name the cells and use the =INDIRECT() function.

But here comes the tricky part.

The list of a given company's contracts is not static, the list grows by time.
Even more, new companies can get in the company list as well.


Currently I use PIVOT to get the necessary data from Workbook_1, and I can refresh it any time I need an updated list.


By chance, do you have any idea how to deal with this situation?

 

[gallen]

Maybe some of the more experienced on here could correct me, but I think you solution will require VBA to achieve. I do a similar thing in one of my workbooks and could only work it out via Macros.

 

[papplaszlodaniel]

Maybe some of the more experienced on here could correct me, but I think you solution will require VBA to achieve. I do a similar thing in one of my workbooks and could only work it out via Macros.

Hi gallen,

Thank you! Yep, I believe it can be handled only with Macros properly. Although I'm really not familiar that much with vba to create a new macro by myself. :/

 

[bhos123]

for a particular company do you want to sum all the contracts?

 

[bhos123]

If Yes, try the below code, assuming you have data in Sheet1

Sub Macro2()

Sheets("Sheet1").Select
Sheets("Sheet1").Copy
ChDir "C:\Users\mpailla\Downloads"
ActiveWorkbook.SaveAs Filename:="C:\Users\mpailla\Downloads\Workbook2.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Result"
Sheets("Result").Select
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A" & lastrow).RemoveDuplicates Columns:=1, Header:=xlNo
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Range("B2").Select
ActiveCell.FormulaR1C1 = "=SUMIF(Sheet1!C[-1],Result!RC[-1],Sheet1!C)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & lastrow), Type:=xlFillDefault
Range("B1").Value = "Total Contracts"

End Sub

 

[papplaszlodaniel]

for a particular company do you want to sum all the contracts?

Hi there

This second workbook (Woorkbook_2) is going to be a register for Completion Certificate.
Meaning I have a project with, let's say, 10 companies. Each company have 1 to 5 contracts. Each contract entitles the given company to issue 5 invoices. For the invoices they will need the Completion Certificate what I'm gonna issue. (--> 50 companies, 150 contracts, 750 completion certificates)

What I would like to do now, when a new request for completion certificate arrives, I would like to put it in the register.
First I select the company from a drop down, then select the relevant contract from another drop down. But the second drop down should only contain the contract numbers of the earlier chosen company.

The troublesome part is that I don't have all the 50 companies and 150 contracts when I start the project, they will come by time during the project.
So these two drop downs will be dependent on each other, AND both have to be able to handle expanding value range.

I hope it clarifies it a little bit.

 

[gallen]

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'

	A	B
1	Company Name	Contract Numbers
2	Company1	12345

<tbody>
</tbody>

Right click the Sheet1 tab name and select 'View Code

Paste this code in tot he window that appears:

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:

	A	B	C	D
1	Company Names		Company Name	Contract Number
2	Company1		Company1	12345
3	Company2		Company2	12346
4	Company3		Company3	12347
5			Company1	12348
6			Company2	12349
7			Company3​	12350

<tbody>
</tbody>

Again right click the Tab name 'Data' and select 'View Code'

Paste this:

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.

 

[gallen]

Apologies, the GetContractNumbers sub falls down if no contract numbers are found so this updated code will handle that error:

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
    If j > 1 Then 'At least 1 Contract has been found
        Sheets("Data").Range("F1:F" & j - 1).Name = "_ContNums" 'Set the name o fthe range to '_ContNums' (contract Numbers)
    Else
        'No Contract Numbers Found
        Sheets("Data").Range("F1").Name = "_ContNums"
    End If
    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

 

[papplaszlodaniel]

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

(...)

Let me know if it's confusing.

Hi gallen,

Wow, thanks! I managed to follow, tested and it worked, superb! Thanks!

Only one question left:
This works neatly on Sheet1 A2 and B2 cells.
Is it possible to manage the same for the rest of the rows on Sheet1 (eg. A2:B750)?

Like: I pick one company and one of its contract number in row2, then I pick another company with another contract number in row3, and so on.

 

[gallen]

Ahh a bit more complicated as we'd need to set the validation dynamically.

If you copy and paste cell A2 in Sheet1 and paste it as many times in to the rows of column A the following code works. Delete all the code behind Sheet1 and paste this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    Select Case Target.Column
        Case 1 ' User has changed a cell in column A
            If Target.Row > 1 Then GetContractNumbers Target.Row
    End Select
    
End Sub

Private Sub GetContractNumbers(iRow As Integer)

'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
Dim iCol As Integer 'column of associated results


    i = 0
    j = 1
    iCol = iRow - 2
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    sCompany = Range("A" & iRow).Value 'the company the user has selected
    Sheets("Data").Range("F:F").Offset(0, iCol).Clear 'empty the column associated with the row
    
    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).Offset(0, iCol).Value = Sheets("Data").Range("C2").Offset(i, 1).Value
            
            j = j + 1 'next row number in column F
        End If
        
        i = i + 1
    Loop
    If j > 1 Then
        Sheets("Data").Range("F1:F" & j - 1).Offset(0, iCol).Name = "_ContNums" & iCol 'Set the name of the range to '_ContNums' (contract Numbers)
    Else
        'No Contract Numbers Found
        Sheets("Data").Range("F1").Offset(0, iCol).Name = "_ContNums" & iCol
    End If
    
    SetValidation iRow, "_ContNums" & iCol 'Sub to dynamically set validation of next dropdown
    If Sheets("Data").Range("_ContNums" & iCol).Cells.Count > 0 Then Range("B" & iRow) = Sheets("Data").Range("_ContNums" & iCol).Cells(1, 1)
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
Exit Sub
errHandle:
    MsgBox Err.Description
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Sub SetValidation(iRow As Integer, sRange As String)

    With Range("B" & iRow).Validation
        .Delete 'clear any validation
        .Add xlValidateList, , xlBetween, "=" & sRange
    End With
    
End Sub

Notice that on the DATA sheet the columns are being filled with the results. Hope this helps. Was a real brain workout!