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
AA
BB
CC
I want to parse the pre-dash values from column A in order to auto-populate the column B drop down list.
LM_GROUPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
MatchingLists.xlsx
0
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.
0
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

[ fanpages ]IT Services ConsultantCommented:
Hi,

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

Exit_cboFirst_Change:

  On Error Resume Next
  
  Exit Sub

Err_cboFirst_Change:

  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.

BFN,

fp.
Q-28256835.xls
0
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).
0
[ 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)?
0
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

Signed,
Working with both hands tied behind my back (but still working)
0
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))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.