Excel/Entering grouped drop down list in excel
QUESTION: Hi Mr. Ostrander, Good day to you.
i want to include a grouped drop down list in excel. forexample name of a contractor and then under that contractor name of its sub-contractors.
i hope i have put my question clearly.
thank you in advance.
ANSWER: Hello Ibrahim,
Would you please clarify your question a bit? I am uncertain whether you want a single dropdown that contains both the contractor and subcontractor names, or cascading (hierarchical) dropdowns.
With cascading dropdowns you would typically have a contractor dropdown such that when you select a contractor from it a subcontractor dropdown pops up with a list of relevant associated subcontractors.
[an error occurred while processing this directive]---------- FOLLOW-UP ----------
QUESTION: Hi Damon, thanks for the reply.
I want list of primary contractors in a drop down list and once the primary contractors are clicked a hierarchical drop down opens.
Hello again Ibrahim,
Okay, here is a solution that is based on using in-cell list data validation to provide the dropdowns. I could have accomplished this using ActiveX or Forms Control comboboxes, but I figured you would have mentioned these if you had preferred to do it this way. However, if you do prefer using comboboxes let me know and I will provide a solution that uses that approach.
I am going to assume that if you have more than one cell containing the list of primary contractors, these cells are in a columnar range, say for example C2:C30. And for now I will assume that the subcontractor cells are one column to the right in D2:D30. But these ranges could each be a single cell.
Start by validating all your contractor cells (again assumed to be C2:C30) by selecting C2:C30 and then menu Data > Validation > Settings tab > Allow: List > Source: =$C$2:$C$30.
Next, validate each sub-contractor list. Pick ranges on you sheet to list the subcontractors. I would suggest columns on your sheet to the right of the part you are already using for data, and later you can hide these columns. Let's say for example contractor 1 is Home Depot, and subcontractors associated with Home Depot are Glidden, Valspar, and Sherwin Williams, and you place these subcontractors in P2:P4. Now for the key to why the code for this is so simple: select P2:P4 and name it "SherwinWilliams" by typing this string in the Name box above cell A1. Note that you will always name each subcontractor list range the name of the prime contractor with spaces removed, which is what enables the code to find the correct associated subcontractor list for each contractor.
Then validate each subcontractor cell by selecting D2:D30 and using as the source ="subcontractor name", where "subcontractor name" is any one of the subcontractor name ranges (example: SherwinWilliams).
Finally place the following code in the worksheet's code module. You can do this by clicking the worksheet's tab, select View Code, and paste this code (including the comments at the end) in the Code pane:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2:$C$30 Then
'Remove spaces from the selected option text so that it can be used
'directly as the name of a range containing the list of sub-options
Dim SubName As String
SubName = Replace(Target.Text, " ", "")
With Target.Offset(0, 1)
.Validation.Modify Formula1:="=" & SubName
'How to use this for a general situation:
' Replace the "Target.Address = "$C$2:$C$30" with a specifier for the range that contains
' the cells that are validated with your first ("superior") choices. For example
' you might have a list of prime contractors in range F11:F400, in which case you
' should change "Target.Column = 1" to "Target.Address = "$F$11:$F$40 0".
' If your second ("subordinate") choice cells are not directly adjacent to the
' right of the first choice cells, change "Target.Offset(0,1)" to the actual
' offset between your first and second choice cells using the second argument
' to specify the number of columns of offset between them. You can use negative
' offset if the second choice column is to the left of the first choice column.
' For example change "Target.Offset(0,1)" to "Target.Offset(0,3)" if the second
' is three columns to the right of the first choice cell.
Unfortunately I was unable to make the subcontractor list automatically drop down when the contractor is chosen, but I did find I could make the cell select and the dropdown arrow appear by activating the cell.
Note that any of the lists can be of virtually any length.
I hope you find this helpful. Feel free to follow up if any problems.