Drop down list to display only grouped items

[DjTRex2002]

So, I have Vendor Codes on "Column A", Part # on "Column B", and Description on "Column C" ... is there a way to create a drop down list to pick from items that belong only to specific vendors? Example,

I want to select Vendor Code MCF1 (sorry that below I have a 2404 instead of MCF1 lol) but then I want the Part # drop menu to display items that belong ONLY to that Vendor ...

This is the list to gather the information from ...

 

[kevin9999]

I want to select Vendor Code MCF1

How do you select it?

 

[roelandwatteeuw]

Hi

Make a filtered result in a free (hidden) space. This can be on another sheet.
E.g. Cell H2 on Sheet1

Formula:

=FILTER(Sheet2!B:B, Sheet2!A:A=Sheet3!D2)

Sheet2 is the sheet with your data (Vendor Code, Part #...)​

A:A is your Vendor Code column​

B:B is your Part # column​

Sheet3 is the sheet with you selection​

Then go to the cell where you need the dropdown
Go in the menu to Data > Data Validation
Select 'List'
Source:

=Sheet1!$H$2#

grtz

 

[DjTRex2002]

Hi

Make a filtered result in a free (hidden) space. This can be on another sheet.
E.g. Cell H2 on Sheet1

Formula:

=FILTER(Sheet2!B:B, Sheet2!A:A=Sheet3!D2)

Sheet2 is the sheet with your data (Vendor Code, Part #...)​

A:A is your Vendor Code column​

B:B is your Part # column​

Sheet3 is the sheet with you selection​

Then go to the cell where you need the dropdown
Go in the menu to Data > Data Validation
Select 'List'
Source:

=Sheet1!$H$2#

grtz

Thank you for your help ... is there a way you could maybe post an example sheet of how this would work? I am trying to figure it out but have no clue lol

 

[kevin9999]

You still haven't explained how you intend to select the vendor, so I'm guessing here. Note the formulas in cells I2 and J2, and the validations in cells E2 and G2. When you select a vendor in E2, cell G2's validation will only allow Part#'s relating to that vendor. For future reference, please look into the XL2BB add in, so helpers don't have to recreate a sample sheet to test possible solutions.

part no.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Vendor Code	Part #	Description		Vendor Choice		Part #		Unique Vendors	Part #
2	MCF1	MCF1 Part 1	MCF1 Part 1 Description		MCF1		MCF1 Part 2		MCF1	MCF1 Part 1
3	456	456 Part 1	456 Part 1 Description						456	MCF1 Part 2
4	789	789 Part 1	789 Part 1 Description						789	MCF1 Part 3
5	MCF1	MCF1 Part 2	MCF1 Part 2 Description
6	456	456 Part 2	456 Part 2 Description
7	789	789 Part 2	789 Part 2 Description
8	MCF1	MCF1 Part 3	MCF1 Part 3 Description
9	456	456 Part 3	456 Part 3 Description
10	789	789 Part 3	789 Part 3 Description
11
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=UNIQUE(A2:A10)
J2:J4	J2	=FILTER(B2:B10,A2:A10=E2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$I$2#
G2	List	=$J$2#

 

[Peter_SSs]

Can't really tell what sheets are involved or what columns/rows in that first image (suggest that you investigate XL2BB) but see if you might be able to use/adapt this.
My Part # Data Validation cells are in column I
Put the formula as shown in D2.
Set up the column I Data validation as shown below the mini Sheet.

GjTRex2002.xlsm
	A	B	C	D	E	F	G	H	I
1	Vendor	Part	Description	Vendor 2			Vendor	Qty	Part #
2	Vendor 3	Part 1	Desc 1	Part 8			Vendor 2
3	Vendor 3	Part 2	Desc 2	Part 13
4	Vendor 5	Part 3	Desc 3
5	Vendor 4	Part 4	Desc 4				Vendor 5
6	Vendor 5	Part 5	Desc 5
7	Vendor 3	Part 6	Desc 6
8	Vendor 1	Part 7	Desc 7
9	Vendor 2	Part 8	Desc 8
10	Vendor 3	Part 9	Desc 9
11	Vendor 3	Part 10	Desc 10
12	Vendor 4	Part 11	Desc 11
13	Vendor 5	Part 12	Desc 12
14	Vendor 2	Part 13	Desc 13
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=FILTER(B2:B14,A2:A14=D1,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2:I14	List	=D$2#

Put this code into the worksheet's code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With ActiveCell
    If .Column = 9 And .Row > 1 Then Range("D1").Value = .Offset(, -2).Value
  End With
End Sub

When a cell in column I is selected, the code puts the corresponding column G (Vendor name) into cell D1 which is used for the formula in D2. This creates the DV list for the drop-down.
As different cells in col I are selected the code changes the D1 value to create the appropriate DV list below. What I have in col D can be anywhere, including on another sheet and/or could be hidden.

Here is what is shown if I5 is selected and the drop-down activated. You can see that Vendor 5 has been entered in D1 and D2 down contains the vendor 2 part numbers which are then shown in the drop-down.

 

[DjTRex2002]

I really appreciate the support. I used the FILTER formula and it works great ... however, when I pick cell F17, while it does show what I need, it also shows a bunch of blanks ... is there a way to not display blanks?

I tried using a New Manager name with formula with =OFFSET(Original!$P$1,0,0,ROWS(Original!$P:$P)-COUNTIF(Original!$P:$P,"")-1,1) for a Validation drop down list, but it won't let me select the cell because it says there is an error

 

[Akuini]

@DjTRex2002
Since you're using Excel 365, please check out this tutorial from Mynda Treacy:
Easy Dynamic Dependent Data Validation Two Ways

 

[Peter_SSs]

Can't tell much from an image but in the Vendor/Part #/Description table (columns A:C in my sample above) do you have rows with "MCF1" with nothing (or formula returning "") in the column beside?
Something like this?

Try replacing this formula with

=FILTER(Parts!B2:B3000,(D17=Parts!A2:A3000)*(Parts!B2:B3000<>""),"")

 

[DjTRex2002]

Can't tell much from an image but in the Vendor/Part #/Description table (columns A:C in my sample above) do you have rows with "MCF1" with nothing (or formula returning "") in the column beside?
Something like this?

View attachment 109287


View attachment 109288
Try replacing this formula with

=FILTER(Parts!B2:B3000,(D17=Parts!A2:A3000)*(Parts!B2:B3000<>""),"")

Hi Peter,

I did that and it works great for sorting out the parts that belong only to the Vendor selected, but my only issues is that when I select the drop down list, it shows a bunch of blanks. I want to be able to only see the items that belong to the vendor. Just like the pic above it shows MCF1 and only has one item, the E576 ... here's another example of different Vendor selected, which has 3 items ... but shows a bunch of blanks below.