Dependent Data Validation dropdown using non-contiguous named ranges

[shstrating]

Environment: Excel 2013 / Win7 Pro – Service Pack 1

I am trying to find a way to get a non-contiguous range of cells to work in a dependent data validation dropdown.

I have set everything up according to the excellent step-by-step at http://www.contextures.com/xlDataVal02.html. My first and second Data Validation dropdowns are working great, but I need to stray from this structure for my 3rd dropdown.

For my 3rd dropdown I want to be able to define multiple named ranges that consist of non-contiguous cells in a Master Product list.

The reason for this quest is that the items in the Master Product list appear in multiple Product Categories and I am hoping I can avoid having to create multiple sub-Master Product lists that contain duplicate items (along with the inherent maintenance issues, opportunities for errors and unsynchronized data that comes with duplication).

I’ve attached some sample data from a much larger data set:

• Product Hierarchy (Yellow area) shows the relationship from top level Product Category, thru mid-level Product Sub-category, down to Products.
• Under DV-1, DV-2 & DV-3 (Blue area) are the Data Validation formulas I’m using from Contextures.
• In ColG (Green area) is the DV-3 Master list consisting of the Product list from ColE with duplicates removed. This is the list I want to create non-contiguous named ranges within.
• In ColH & ColI (Green area) are the cell references that I need for my non-contiguous named ranges from the DV-3 Master list.

Excel 2012

	A	B	C	D	E	F	G	H	I
1			Product Hierarchy				DV-1	DV-2
2	Product Category		Product Sub-category		Products		=ProductList	=INDIRECT(VLOOKUP (P1,ProductLookup,2,0)&"List")
3
4					Admin/Median		DV-3
5					Desks		=INDIRECT(VLOOKUP(Q1,INDIRECT(VLOOKUP (P1,ProductLookup,2,0)&"Lookup"),2,0)&"List")	What should this formula be so that I can use the non-contiguous ranges below?
6			Panels-Based Systems		Electrical/Data Components
7					Panels		DV-3 Master list	Panels-Based Systems	Linked Desks & Benching
8					Privacy Screens		Admin/Median	G8	G9
9							Base Assemblies	G10	G11
10					Base Assemblies		Desks	G11	G14
11					Electrical/Data Components		Electrical/Data Components	G13	G15
12	Systems		Linked Desks & Benching		Privacy Screens		Horizontal Technology Channel	G14	G16
13					Systems Accessories		Panels		Horizontal Technology Distribution
14					Systems Fabric Board		Privacy Screens		G10
15							Systems Accessories		G11
16					Desks		Systems Fabric Board		G12
17					Electrical/Data Components				G13
18			Horizontal Technology Distribution		Horizontal Technology Channel				G14
19					Panels
20					Privacy Screens

<tbody>
</tbody>

Sheet3​

I’m trying to figure out what formula to use in the DV-3 dropdown that will enable me to use the non-contiguous named ranges in ColI thru ColK.

I’ve been searching MrExcel and the web in general with no luck. I think I need to get better at entering well defined search strings for what I’m after.

Any ideas on how to do this within the confines of a single Master Product list (avoiding helper columns, sub-Master Product lists, or VBA of which I know almost nothing)?

Thank you for whatever guidance you can give.

Steve

 

[shstrating]

Just a gentle nudge in the hope that someone has an alternative to using the INDIRECT function to 'assemble' a non-contiguous range of cells into a list that can be used in a dependent data validation dropdown.

Thanks so much for any help you can give.

Steve