Dynamic (dependent) validation drop down without named ranges

[Wheeler55]

Hi, I need to be able to define a dependent drop down using a lookup with contents based on another column. All the posts I've searched show how to do this by defining different named ranges - eg if there are 6 values in the first column, and you want the second column to show only data from a list with the selected value, then you must define 6 named ranges and use INDIRECT. Which is a great approach but doesn't solve my problem.

I have on one tab a Product list with two columns. The first is Product Category, the second is Product Name. There are say 1000 combinations, with about 100 Product Categories. So it's not possible to define a named range for each product category.
In the main s/sheet where I want to use this data against particular assets:
Columns A-C (Say) have data about the individual asset.
Column D - is used to select the Product Category via data validation drop down (reference to a list)
Column E - I want to be able to have a data validation drop down showing all the Product Names for the given Product Category (ie dependent). ie somehow list only the Product Names for the chosen Product Category.

Is there any way not using VBA to do this given the Product list will change from time to time (so can't predefine any range except the whole list) . Hope this is clear - my first post. Thanks for any help.

 

[BrianMH]

Yes it is possible but the only way I can think of used a dynamic range and the list MUST be sorted by product category. You will need to maintain a separate list of individual product categories for that dropdown.

Given the layout below the data validation formula for E4 would be =OFFSET(INDEX(B:B,MATCH(D4,A:A,0)),,,COUNTIF(A:A,D4))

Excel Workbook
	A	B	C	D	E
1	Product Category	Product Name
2	Category 1	Product 1
3	Category 1	Product 2		Category Dropdown	Name Dropdown
4	Category 1	Product 3		Category 2
5	Category 1	Product 4
6	Category 2	Product 5
7	Category 2	Product 6
8	Category 2	Product 7
9	Category 2	Product 8
10	Category 2	Product 9
11	Category 2	Product 10
12	Category 3	Product 11
13	Category 3	Product 12
14	Category 3	Product 13
15	Category 3	Product 14
Sheet1

 

[Wheeler55]

Thanks for the quick response. Will give that a go at work tomorrow.

 

[Wheeler55]

Yep worked like a dream thanks Brian. I hadn't seen the "B:B" range notation before either so learnt that too.
Many thanks.....Ken.