Can I create a dropdown list using some kind of index match?

[tonywatsonhelp]

Hi Everyone,

I'm putting together an invoice that will select a product in Cell J8, (this is simply done with a dropdown box and is all set up!)
Now K8 is the optional extras and this is where I need help

The optional extras are all different depending on what product is chosen

In sheet "Products" I have a list of all the products in Column A and in B the optional extras.
For Example A2 = "Bycicle Lamp1" and B2 = "With Brackets, Without Brackets, Small Bracket"

Now I know I can set up a data validation list by typing the list into data validation, so I was wondering if I could do an Indirect with an index match to use the data in a cell as that list?

any ideas how I can do this?

Thanks

Tony

 

[L. Howard]

Looks like you want a dependent drop with J8 as the primary drop down and K8 as the dependent drop down which will offer up a list pertinent to the J8 selection.

A very common situation, perhaps you already know of. The rub comes when the "dependent list" is all in one cell.

If you are NOT aware of the common dependent drop down scenario, and can alter the sheet "Products" data, then should be pretty easy to do.

Are you at liberty to take the one-cell list "With Brackets, Without Brackets, Small Bracket" and put it in a column below the header "Bycicle Lamp1"? (Along with all the other J8 items done similarly on sheet "Products"?)

I don't know of a way short of a complex VBA solution to do a text-to-columns and transpose process for the one-cell lists to convert them to a column list, or perhaps the use of "split" in VBA to get your lists out of one cell and into a column to be accessed as the source in the dependent drop down.

I most likely could not develop a code to do that, although there are some on the forum who could probably make short work of it. I would suggest the columns and the use of INDIRECT in the dependent drop down source window.

Howard

 

[estevaoba]

Hello! Let's try this.

In PRODUCTS Sheet, use the formula below to create a horizontal list with extras pertinent to product selected in your validation cell J8. It’s an array formula, so after you paste it, make sure you hold down ctrl and shift while you hit Enter key. Copy it across as many columns as the maximum number of extras.
I named sheets like that, just for an example. Adapt to suit.

=IFERROR(INDEX(PRODUCTS!$A$2:$F$100,MATCH(VALIDATION_SHEET!$J$8,PRODUCTS!$A$2:$A$100,0),COLUMN(B$1)),"")

Then tap Formulas menu, then Names Manager (or Titles Manager, I’m not sure what they call it in English). Tap New. Name it Extras, or another name you like, as long as you don’t use spaces or special characters. In “Refers to”, paste formula below.
Same as above, check sheets’ names and range (it is the range you pasted formula above).

=OFFSET(PRODUCTS!$G$2,,,1,COUNTA(PRODUCTS!$G$2:$L$2)-COUNTIF(PRODUCTS!$G$2:$L$2,""))

Finally, tap cell K8 and, in the Validation Data dialog box (assuming you have selected List in the "Allow" field), in "Source" field, type:

=Extras
(or whatever name you chose in Names Manager)

I hope it will work!

Godspeed!

 

[L. Howard]

Turns out the macro route was not that complicated, if you want to give it a try do this.

You already have a primary drop down in sheet1 (or whatever the name is) in cell J8.
Now do a drop down in K8 > Allow > List > Source > =OFFSET(Products!$C$2,0,0,COUNTA(Products!$C:$C),1)

On sheet Products you will have your list of all the items that are in the primary J8 drop down list and the comma separated optional extras in column B.
Column C will need to be free to accept the optional extras for whatever item is selected in the primary drop down (J8 sheet1).

The code will clear the C column previous optional extras and supply a new list for the new selection. This will happen automatically when the drop down selection is made on sheet1. The K8 drop down will be cleared and cell K8 will be selected, and you will click the down arrow for a selection from the new list.

In the sheet1 module (or whatever the name is) copy and paste this code below.

Howard

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$J$8")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub


  Dim ddFound As Range
  Dim dDwn As String
  Dim X As Variant
  
  
   dDwn = Sheets("Sheet1").Range("J8")


With Sheets("Products")


   .Range("C2", .Cells(.Rows.Count, .Columns.Count)).Clear


   Set ddFound = Sheets("Products").UsedRange.Find(What:=dDwn, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
 
 
 
   If Not ddFound Is Nothing Then


       X = Split(ddFound.Offset(, 1).Value, ",")
       Sheets("Products").Range("C2").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
   
     Else


      MsgBox "No match found."
    
   End If


End With


[K8].ClearContents
[K8].Select


End Sub

 

[L. Howard]

Here is a bit more tidy code than the one in my post #4 .

Discard the previous, and use this one for in your VBA solution. NOTE: You cannot have two change_event codes in the same module, ONE only!

Howard

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$J$8")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim oneRng As Range, ddFound As Range
Dim dDwn As String
Dim X As Variant
  
dDwn = Sheets("Sheet1").Range("J8")
      
With Sheets("Products")
   
     .Range("C2", .Cells(.Rows.Count, .Columns.Count)).Clear
   
   Set ddFound = .Range("A:A").Find(What:=dDwn, _
                     LookIn:=xlValues, _
                     LookAt:=xlWhole)

   If Not ddFound Is Nothing Then

      X = Split(ddFound.Offset(, 1).Value, ",")
     .Range("C2").Resize(UBound(X) + 1).Value = Application.Transpose(X)

   Else

      MsgBox "No match found."

   End If

End With

[K8].ClearContents
[K8].Select
End Sub

 

[tonywatsonhelp]

Brilliant,
Thank you L Howard for your time and code
and Estevado for your ideas.

I think I have lots to try and play about with now so I'm sure I'll get this working.
thanks you very much for your advice

Tony

 

[L. Howard]

You're welcome, and let us know how it works out for you.

Howard