Dropdown List from Named Selection but Filtered

[jordtee]

Hello,

I have a table with columns "product" and "category" and I have data validated dropdown lists where the user chooses the product they would like for each category. The end goal is for the data validation list to only offer the user products that fall under the correct category in the column to the left. The number of products and the number of categories can increase or decrease at any time.

I have been utilizing a (more) complex solution than I hope to end up with: use power query to create a filtered table from the main table with only the product column; assign it a named range; use that named range in the dropdown list for that category. Is there a simpler solution than going through the power query steps for every category? Can I put a filter in the data validation list selection?

Book1
	A	B	C	D
1
2	Choose a product for each category
3		Grounding	¯
4		Doors	¯
5		Handles	¯
6
7
8
9
10
11	product	category
12	1	Doors
13	2	Doors
14	3	Grounding
15	4	Handles
16	5	Handles
17	6	Grounding
Sheet1

This is what the power query tables look like. (these have actual data, hence the difference in "products")