Filtered Drop Down List based on Criteria

[carbs82]

Need help to create a drop-list to only show Part No related to Supplier

tbl_supplier_partno (table is dynamic)
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]Supplier
[/TD]
[TD="align: center"]Part No
[/TD]
[/TR]
[TR]
[TD="align: center"]K MART[/TD]
[TD="align: center"]111[/TD]
[/TR]
[TR]
[TD="align: center"]TARGET
[/TD]
[TD="align: center"]222[/TD]
[/TR]
[TR]
[TD="align: center"]TARGET[/TD]
[TD="align: center"]333[/TD]
[/TR]
[TR]
[TD="align: center"]COSTCO[/TD]
[TD="align: center"]444
[/TD]
[/TR]
[TR]
[TD="align: center"]K MART[/TD]
[TD="align: center"]555
[/TD]
[/TR]
</tbody>[/TABLE]


$H$8: K MART
[TABLE="class: grid, width: 170"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: center"]Supplier:[/TD]
[TD="align: center"]K MART
[/TD]
[/TR]
</tbody>[/TABLE]


What formula do I use in data validation so that the drop-down list will only show parts supplied by K MART?[TABLE="class: outer_border, width: 80"]
<tbody>[TR]
[TD="align: center"]111[/TD]
[/TR]
[TR]
[TD="align: center"]555[/TD]
[/TR]
</tbody>[/TABLE]

 

[rpaulson]

try this

Sub Macro1()
[M1].Validation.Delete

Data = ""
For r = 1 To 10
If Cells(r, "A") = [H8] Then Data = Data & Cells(r, "B") & ","
Next r


If Data = "" Then Exit Sub
With [M1].Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Data
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
End With

End Sub

hth,
Ross

 

[carbs82]

I may not have explained myself correctly.

On F3 is a drop-down of Part_Nos.

What do I do so that the drop-down will only show parts supplied by K MART?

Note that A1:C:6 is a table > table_1

[B]Excel 2013/2016[/B][TABLE]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Supplier[/TD]
[TD="align: center"]Part No[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"][/TD]
[TD]Supplier:[/TD]
[TD="bgcolor: #FFFF00"]K MART[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]K MART[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]PEN[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]TARGET[/TD]
[TD="align: center"]222[/TD]
[TD="align: center"]PENCIL[/TD]
[TD="align: center"][/TD]
[TD]Part No:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]333
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]TARGET[/TD]
[TD="align: center"]333[/TD]
[TD="align: center"]RULER
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]COSTCO[/TD]
[TD="align: center"]444[/TD]
[TD="align: center"]RULER[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]K MART[/TD]
[TD="align: center"]555[/TD]
[TD="align: center"]PENCIL[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet1[/B][/COLOR][/CENTER]

 

[rpaulson]

Sub Macro1()
[F3].Validation.Delete

Data = ""
For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(r, "A") = [F1] Then Data = Data & Cells(r, "B") & ","
Next r


If Data = "" Then Exit Sub
With [F3].Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Data
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
End With

End Sub

 

[carbs82]

Are you stating that this could only be done via macro?

I was hoping to do it just using data validation..

 

[Yongle]

Sometimes VBA is the easiest way but there are usually non-VBA solutions that work equally well

Have a look at this excellent guide to dependent dropdowns and then come back if you need further help

 

[strooman]

Setup:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#0070C0"]Supplier[/td][td="bgcolor:#0070C0"]Part[/td][td="bgcolor:#0070C0"]Description[/td][td][/td][td="bgcolor:#0070C0"]Supplier[/td][td="bgcolor:#0070C0"]Count[/td][td][/td][td="bgcolor:#0070C0"]Supplier[/td][/tr]
[tr][td]

2​

[/td][td]K MART[/td][td]

111​

[/td][td]PEN[/td][td][/td][td="bgcolor:#FFFF00"]COSTCO[/td][td="bgcolor:#FFFF00"]

1​

[/td][td][/td][td]K MART[/td][/tr]
[tr][td]

3​

[/td][td]TARGET[/td][td]

222​

[/td][td]PENCIL[/td][td][/td][td][/td][td][/td][td][/td][td]TARGET[/td][/tr]
[tr][td]

4​

[/td][td]TARGET[/td][td]

333​

[/td][td]RULER[/td][td][/td][td="bgcolor:#0070C0"]Supplier[/td][td="bgcolor:#0070C0"]Part[/td][td][/td][td]COSTCO[/td][/tr]
[tr][td]

5​

[/td][td]COSTCO[/td][td]

444​

[/td][td]RULER[/td][td][/td][td]COSTCO[/td][td]

444​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]K MART[/td][td]

555​

[/td][td]PENCIL[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H2 and down create a unique list with your Suppliers, in this case that's only: K MART, TARGET, COSTCO
Select A1:B6 | hit Ctrl+Shift+F3 | mark -> Top row
Select E2 | hit Alt, A, V, V | Allow -> List | Source -> =$H$2:$H$4
Select F2 formula -> =COUNTIF(Supplier;E2)
Select E5 formula -> =IF(ROWS(E$5:E5)<=$F$2;INDEX(INDIRECT(E$4);SMALL(IF(Supplier=$E$2;ROW(Supplier)-ROW($A$2)+1);ROWS(E$5:E5)));"")

This is an array formula, you have to confirm it with Ctrl+Shift+Enter, not just Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.
Copy to the right and down.