Dynamic dropdown list for large number of items

[BJQQQ]

Hello
I would like to generate a dynamic dropdown list (with "Strength" subject to the selection of "product name". I used the method described in How to quickly create dynamic drop down list in Excel?.

However, as shown in the Table, there are many products with spaces and commas, making the cell naming process complicated. Besides, most of the items only have one option and the list is very long to name cell one by one.

What is the easiest way of dealing with this?

Thank you!

Sample data

Product name	Strength	Form
Aciclovir	200 mg	TAB-CAP
Amlodipine	5 mg	TAB-CAP
Amitriptyline	25 mg	TAB-CAP
Amoxicillin	250 mg	TAB-CAP
Amoxicillin	500 mg	TAB-CAP
Amoxicillin	250 mg/5 ml	SUSPEN
Artemether+Lumefantrine	20 mg+120 mg	TAB-CAP
Acetylsalicylic Acid	100 mg	TAB-CAP
Atenolol	50 mg	TAB-CAP
Penicillin, Benzathine Benzyl	2.4M IU	POWDER
Beclometasone	100 mcg/dose	INHALER
Captopril	25 mg	TAB-CAP
Carbamazepine	200 mg	TAB-CAP
Ceftriaxone	1 g	VIAL
Ciprofloxacin	500 mg	TAB-CAP

<tbody>
</tbody>

 

[MickG]

How many drop down do you want , one for each column ??? ,or something else.
Please show/describe what you want and an example of the results you expect to see for a specific selection

 

[BJQQQ]

How many drop down do you want , one for each column ??? ,or something else.
Please show/describe what you want and an example of the results you expect to see for a specific selection

Thanks for following this up.
I would like three dropdown lists, as shown. For example, when selecting Amoxicillin in B1, I would like: Cell B2 to have a dropdown list for 250mg, 500mg and 250mg/5mL. Cell B3 will be based on selection in cell B2. Based on How to quickly create dynamic drop down list in Excel?., I could name cell range for amoxicillin and link data validation to the name. But I have a large number of products and some products have spaces and commas, making the naming process cumbersome.

thanks for helping and I hope this is clear.

Column A PRODUCT NAME	Column B Amoxicillin
STRENGTH	500 mg
FORM	TAB-CAP

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[dracer]

Since the data in one column relies on the other column this can get complicated, but definitely doable. Try looking at this video by Mike Girven from excel is fun. He does a thorough job explaining this concept and it seems to line up with what you need. It's a little long. He has accompanying worksheets somewhere on the site. I have used this in creating my own dynamic lists. It's good to keep this video for reference even if you don't use it now.

https://www.youtube.com/watch?v=5ko5ES8ZLuY&list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci&index=15

 

[MickG]

1) Place your Data in sheet1 columns "A to C"
2) Copy code below.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng         As Range
Dim Dn          As Range
Dim Dic         As Object
If Target.Address(0, 0) = "B1" Then
    With Sheets("Sheet1") 'Change this to sheet with data
    Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
      End With
        Set Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
For Each Dn In Rng: Dic(Dn.Value) = Empty: Next
With Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
End With
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dn              As Range
Dim Rng             As Range
Dim Dic             As Object
Dim n               As Integer
Dim rS              As Integer
Dim jRw             As String
Dim jTg             As String
If Not Intersect(Target, Range("B1:B2")) Is Nothing Then
  Application.EnableEvents = False
   For n = 1 To 3 - Target.Row
        Target.Offset(n).Validation.Delete
        Target.Offset(n).Value = ""
   Next n
Application.EnableEvents = True
With Sheets("Sheet1")
Set Rng = .Range(.Cells(2, Target.Row), .Cells(Rows.Count, Target.Row).End(xlUp))
End With
  Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   If Not Rng(1).Offset(1) = "" Then
   For Each Dn In Rng
       rS = Target.Row - 1
       If rS > 0 Then
       jRw = Join(Application.Transpose(Application.Transpose(Dn.Offset(, -rS).Resize(, rS + 1))))
       jTg = Join(Application.Transpose(Target.Offset(-rS).Resize(rS + 1)))
        Else
       jRw = Dn.Value: jTg = Target.Value
       End If
      
        If jRw = jTg Then
         If Not Dic.exists(Dn.Offset(, 1).Value) Then
                 Dic(Dn.Offset(, 1).Value) = Empty
            End If
        End If
      Next Dn
   
If Dic.Count > 0 Then
 With Target.Offset(1).Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
End With
End If
End If
End If
End Sub

Open sheet 2 and Right click sheet tab.
From dropdown Select "View Code" , vbwindow appears.
Paste code into vbwindow.
Close Vbwindow

Double click in cell "B1", Validation drop down appears in "B1"
Select value from drop down, Validation appears in "B2"
Select value from "B2", Validation appears in "B3"

Change selection as Required
NB:- This code will not work on a "Mac"

 

[BJQQQ]

Awesome!!! Thank you.