Conditional Drop Down lists - with dynamically changing values

P

papplaszlodaniel

New Member
Joined
May 13, 2014
Messages
10
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?
 
Sort by date Sort by votes
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.
 
Upvote 0
gallen said:
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.
Click to expand...

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. :/
 
Upvote 0
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
 
Last edited:
Upvote 0
bhos123 said:
for a particular company do you want to sum all the contracts?
Click to expand...

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. :)
 
Upvote 0
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.
 
Upvote 0
Apologies, the GetContractNumbers sub falls down if no contract numbers are found so this updated code will handle that error:

Code: 
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
 
Upvote 0
gallen said:
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.
Click to expand...

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.
 
Upvote 0
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:

Code: 
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Drop Down List - to auto Hide rows
Replies
3
Views
244
kevin9999
K
N
  • Question Question
EXCEL VBA COMBO BOX/DROP DOWN LIST
Replies
1
Views
480
Peter_SSs
Peter_SSs
R
  • Question Question
Add to Data Validation Values From Data Entry Cell
Replies
4
Views
419
kvsrinivasamurthy
K
J
  • Question Question
Drop Down that can have data pasted into it without losing the drop down
Replies
1
Views
196
Rijnsent
Rijnsent
F
  • Solved
Choosing multiple items from drop down list
Replies
4
Views
500
Fdjaynes
F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top