• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

MS Excel to auto populate a drop down list based on parsing another drop down list

I want to auto populate an excel drop down list based on parsing the first dash (-) of another drop down list

The first (column A) dropdown list contains values such as
AA - 1234
BB - 2345
CC - 1234

The second (column B) drop down list contains the values
I want to parse the pre-dash values from column A in order to auto-populate the column B drop down list.
  • 4
  • 2
  • 2
  • +1
1 Solution
Steven HarrisPresidentCommented:
If you are wanting to parse the first two letters like you have show here:

If AA-1234 is selected, the box is populated with AA, then use this in place of the second validation box:

=LEFT(cell, 2)

where cell is the location of your first validation list.

If you have many values that need to be populated, take a look at the attached workbook.
LM_GROUPAuthor Commented:
I am already successfully parsing the first column with "AA", if "AA-1234" is selected from the first column drop down and putting the "AA" value in column #2, but #2 is not now a drop down list.

I am now required to make the the second column a drop down list.

I need "AA" to be auto-selected in the drop down list in column #2,  if "AA-1234" is selected in the drop down list from column #1.

The column #1 drop down selection determines which choice is selected in drop down list #2.
Steven HarrisPresidentCommented:
To make sure I am clear:

If AA - 1234 is selected, there are multiple choices populated in the next dropdown box, such as: AA, BB, CC, etc.

If this is the case, see the attached workbook that I provided.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

[ fanpages ]IT Services ConsultantCommented:

I think I understand what you are trying to do now.

You wish to loop through every entry in the second list (that will be a drop-down listbox) & automatically select a value derived from the previously selected entry in the first listbox.

I am (still) unsure if you are using Data Validation, or not, though.

The attached workbook demonstrates Data Validation (in cells [A1] & [B1] of the worksheet [Q_28256835]) & drop-down (Form) combobox controls in cells [E1] & [F1].

The Visual Basic for Applications code within the code module for the [Q_28256835] worksheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

  On Error Resume Next

  If Target.Address = [A1].Address Then
     [B1] = Trim$(Left$(Target, InStr(Target & "-", "-") - 1))
  End If
End Sub

Open in new window

The code from the "basQ_28256835" code module is as follows...

Option Explicit
Public Sub cboFirst_Change()

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28256835.html ]
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
' ID:               Q_28256835
' Question Title:   MS Excel to auto populate a drop down list based on parsing another drop down list
' Question Asker:   LM_GROUP                                  [ http://www.experts-exchange.com/M_4229627.html ]
' Question Dated:   2013-10-03 at 13:11:12
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim lngIndex                                          As Long
  Dim strValue                                          As String
  Dim vntValue                                          As Variant
  On Error GoTo Err_cboFirst_Change
  strValue = ActiveSheet.Shapes("cboFirst").OLEFormat.Object.List(ActiveSheet.Shapes("cboFirst").OLEFormat.Object.Value)
  strValue = Trim$(Left$(strValue, InStr(strValue & "-", "-") - 1))
  ActiveSheet.Shapes("cboSecond").OLEFormat.Object.ListIndex = 0&
  If Len(Trim$(strValue)) > 0 Then
     lngIndex = 0&
     For Each vntValue In ActiveSheet.Shapes("cboSecond").OLEFormat.Object.List
         lngIndex = lngIndex + 1&
         If CStr(vntValue) = strValue Then
            ActiveSheet.Shapes("cboSecond").OLEFormat.Object.ListIndex = lngIndex
            Exit For
         End If ' If CStr(vntValue) = strFind_Value Then

     Next vntValue
  End If ' If Len(Trim$(strValue)) > 0 Then


  On Error Resume Next
  Exit Sub


  On Error Resume Next
  Resume Exit_cboFirst_Change
End Sub

Open in new window

Please look at the two options (cells [A1] & [B1], and cells [E1] & [F1]), & let me know if either approach is what you intended.

Thank you.


LM_GROUPAuthor Commented:
Sorry for not stating this earlier, but because of server security restrictions, that I have no control over, uploaded files that contain macros are blocked, therefore I cannot use your code, thanks.
I provide a spreadsheet that the user downloads from our server to theirs, fills in and the uploads back to our server, their server blocks macros
("and that's  all I have to say about that" - Forrest Gump).
[ fanpages ]IT Services ConsultantCommented:
...humour me a little, in case there is something we can do for you;

What data entry method are using using...

Data Validation (i.e. cells [A1] & [B1] in my attachment above), or
Form Controls (i.e. cells [E1] & [F1] in the same attachment)?
LM_GROUPAuthor Commented:
I can only tell you that the entity to which I provide support prohibits file uploads that contain embedded code, it is seen as a viral attack and blocked, neither option can be used. I am limited to only using formulas. Thank You for your help

Working with both hands tied behind my back (but still working)
LM_GROUPAuthor Commented:
I was able to force the formula below into the B1 column, even though it threw an error because the values are restricted to the drop down list. So now if AA-1234 is selected in A1, then AA is auto-selected in the B1 dropdown. I had been attempting this solution from the start but was cancelling it when the error message displayed, I decided to try to save it anyway and it worked.

=TRIM(LEFT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now