Dynamic dropdown list based on filtered table

[BrendanDixon]

Hi All,

I hope I will be able to explain this clearly. I have a workbook where one sheet has the suppliers names, it also has a column for the category that the company will fall under. I have created a filter where i can filters what categories I want to show. this is simple.

Where I am struggling is that on another sheet I have some cells with data validation list pointing to the names for the companies. This will bring the complete list of companies in the list. The buyers have asked if this list can be filtered with only the companies that they select the categories so that they do not have to go through the whole list.

I am unable to work out a way to do this. please note that they may want to select multiple categories. I have seen methods with helper columns that use a separate dropdown cell to select a category and then only show these companies. But we may not want to filter, and may want to show more than one category.

 

[Georgiboy]

What version of Excel do you use, 2019, 365?

How will the user select more than one category, dropdowns, filtering a table?

If you can use the XL2BB add in, you can display the layout of the sheets you are working with.

 

[BrendanDixon]

Procurement Templates V1.09a.xlsm
	B	C	D
6	Company 1
7	Company 4
8	Company 10
9	Company 8
10
11
12
13
14
15
Input

Cells with Data Validation
Cell	Allow	Criteria
B6:D15	List	=Suppliers!$B$2:$B$1001

Procurement Templates V1.09a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Company Name	Contact Person	Email	Address 1	Address 2	Postcode	BBBEE Level	50% Black Youth Owned	VAT Registered	CSD Number	Category
2	1	ABANTU GENERAL		abantugeneralcontractors@gmail.com				5	NO	YES		BEARINGS
3	2	ACTOM		vickash.ramsern@actom.co.za					NO	YES		ELECTRICAL
4	3	AJM		shaun@ajmengineering.co.za	PO BOX 752037	GARDENVIEW	2047	2	NO	YES		CRANES
5	4	ALL ELECTRICAL		leslie@allelectrical.co.za					NO	YES		ELECTRICAL
6	5	AMAZIMZIM		jimmy.mbhele@gmail.com						YES
7	6	Amber Skies	Craig							YES
8	7	ARGON ARC		amanda@argonarc.co.za					NO	YES		WELDING
9	8	AUTOTRONIX		brandon@autotronix.co.za						YES
10	9	BC INDUSTRIAL		collin@bcindustrial.co.za				1	NO	YES		GENERAL
11	10	BC INDUSTRIAL		innocent@bcindustrial.co.za				1	NO	YES		GENERAL
12	11	BLASTRITE		sales@blastrite.com					NO	YES		SHOTBLAST RELATED
13	12	BOILER AND INDUSTRIAL							NO	YES		GAS REPAIRS
14	13	CCSA Industrial Supplies	Cyril							YES
15	14	COMBUSTION AND GAS SERVICES							NO	YES		GAS REPAIRS
16	15	DC TSAKANE		vickus@tsakanedjc.com						YES
17	16	DDS INDUSTRIAL		sai@ddsindustrial.co.za				1	NO	YES		TOOLS
18	17	DONKIN (ESAB)		fran.mclean@esab.co.za					NO	YES		WELDING
19	18	DVK						5	NO	YES		BEARINGS
20	19	ECORAIL		ecorail706@gmail.com					NO	YES		ELECTRICAL
21	20	ELEC SUPPLIES CORP		ajith@escdbn.co.za					NO	YES		ELECTRICAL
22	21	EUROSUPPLIES	Gordon	gordon.voltmech@mweb.co.za	address 1	adress 2	3610	2	NO	YES		ELECTRICAL
23	22	FIKILE		terrence@fikileindustrialsupp.co.za				1	NO	YES		GENERAL
24	23	FOGARTY ELECTRONICS		pro@fogarty.co.za					NO	YES		ELECTRICAL
25	24	ISIZWE							NO	YES		GAS REPAIRS
26	25	Iswele Trading Enteprise	Mong							YES
27	26	KONE CRANES		shane.beukes@konecranes.com						YES		CRANES
28	27	KUHN & REINHARDT							NO	YES		GAS REPAIRS
29	28	Machine Tool Promotions	Gert					1	NO	YES
30	29	MACHINE TOOL REFABRICATION		shelia10@telkomsa.net				1	NO	YES		TOOLS
31	30	Mafema Sales and services	Lucius							YES
32	31	MAGNET ELECTRICAL		bevo@magnetgroup.co.za					NO	YES		ELECTRICAL
33	32	MAGNUM PRECISION TOOLS		kim@magnumtools.co.za				1	NO	YES		TOOLS
34	33	MELRON GROUP							NO	YES		GAS REPAIRS
35	34	Memotek Trading	John							YES
36	35	NGALA MARKETING		ngalaagency@gmail.com				1	NO	YES		TOOLS
37	36	NUFORM MARKETING		nuform31@gmail.com				1	YES	YES		TOOLS
38	37	OCA		jaco.bosch@ocaglobal.com						YES
39	38	OCEANLAND		oceanlandelectrical@hotmail.com					NO	YES		WELDING
40	39	OCEANLAND		oceanl@mweb.co.za					NO	YES		WELDING
41	40	Phawu Construction								YES
42	41	PHUMELELA GASINGA	Prinolin	phumelelagasinga@gmail.com				1	YES	YES		SHOTBLAST RELATED
43	42	PLASONIC		clifford@plasonicwelding.co.za					NO	YES		WELDING
44	43	PLASONIC		admin@plasonicwelding.co.za					NO	YES		WELDING
45	44	PN CABLES	Preggie	preggienaidoo63@gmail.com				2	NO	YES		ELECTRICAL
46	45	PORT ELECTRICAL		sunil@portelectrical.co.za					NO	YES		ELECTRICAL
47	46	POWERTRADE		powertrade@mweb.co.za				1	NO	YES		ELECTRICAL
48	47	PR ELECTRICAL		prelectricalsupplies@gmail.com					NO	YES		ELECTRICAL
49	48	PREMIUM INDUSTRIAL AGENCY		gavin@premiumagencies.co.za				1	NO	YES		TOOLS
50	49	PREVEN TRADING		preventrading@gmail.com				1	YES	YES		GENERAL
Suppliers

Cells with Data Validation
Cell	Allow	Criteria
I2:J50	List	YES,NO
L2:L50	List	=Categories!$B$4:$B$103

I see the XL2BB does not show that the headings on the Suppliers list has a filter on it

 

[BrendanDixon]

Just for everyones reference I managed to find a solution.

I followed these instructions and i am now sorted.

Excel Drop Down from Filtered List

 

[Georgiboy]

Excellent, thanks for posting the solution..