You are here:

Excel/Entering grouped drop down list in excel

Advertisement


Question
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.

Damon

---------- 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.

Regards

Answer
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
        .Activate
     End With

  End If
End Sub

'How to use this for a general situation:

'1.
' 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".

'2.
' 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.

Damon  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.