Drop down list to display only grouped items

DjTRex2002

New Member
Joined
Mar 22, 2024
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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 ...

1711549104276.png


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

1711548909097.png
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi

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

Formula:
Excel 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:
Excel Formula:
=Sheet1!$H$2#

grtz
 
Upvote 0
Hi

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

Formula:
Excel 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:
Excel Formula:
=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
 
Upvote 0
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
ABCDEFGHIJ
1Vendor CodePart #DescriptionVendor ChoicePart #Unique VendorsPart #
2MCF1MCF1 Part 1MCF1 Part 1 DescriptionMCF1MCF1 Part 2MCF1MCF1 Part 1
3456456 Part 1456 Part 1 Description456MCF1 Part 2
4789789 Part 1789 Part 1 Description789MCF1 Part 3
5MCF1MCF1 Part 2MCF1 Part 2 Description
6456456 Part 2456 Part 2 Description
7789789 Part 2789 Part 2 Description
8MCF1MCF1 Part 3MCF1 Part 3 Description
9456456 Part 3456 Part 3 Description
10789789 Part 3789 Part 3 Description
11
Sheet1
Cell Formulas
RangeFormula
I2:I4I2=UNIQUE(A2:A10)
J2:J4J2=FILTER(B2:B10,A2:A10=E2,"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
E2List=$I$2#
G2List=$J$2#
 
Upvote 0
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
ABCDEFGHI
1VendorPartDescriptionVendor 2VendorQtyPart #
2Vendor 3Part 1Desc 1Part 8Vendor 2
3Vendor 3Part 2Desc 2Part 13
4Vendor 5Part 3Desc 3
5Vendor 4Part 4Desc 4Vendor 5
6Vendor 5Part 5Desc 5
7Vendor 3Part 6Desc 6
8Vendor 1Part 7Desc 7
9Vendor 2Part 8Desc 8
10Vendor 3Part 9Desc 9
11Vendor 3Part 10Desc 10
12Vendor 4Part 11Desc 11
13Vendor 5Part 12Desc 12
14Vendor 2Part 13Desc 13
Sheet1
Cell Formulas
RangeFormula
D2:D3D2=FILTER(B2:B14,A2:A14=D1,"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
I2:I14List=D$2#


Put this code into the worksheet's code module.

VBA Code:
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.

1711865895272.png
 
Upvote 0
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?

1711983750604.png


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

1711983678805.png
 

Attachments

  • 1711983602018.png
    1711983602018.png
    48.6 KB · Views: 15
Upvote 0
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?

1712019174310.png



1712019337640.png

Try replacing this formula with
Excel Formula:
=FILTER(Parts!B2:B3000,(D17=Parts!A2:A3000)*(Parts!B2:B3000<>""),"")
 
Upvote 0
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
Excel Formula:
=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.

1712065321090.png
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,109
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top