Data Validation - Drop Down - One or More dependent Drop Down Values

[MikeLevine]

Use case (Named Ranges and Table)

Data Table (can be on same or different worksheet - for this post, all my testing is on the same worksheet

PRODUCTS (column H)	PROCESSES (column I)	TOPICS (Column J)
A	Process_1	How2Process_1
A	Process_2	How2Process_2
B	Process_10	How2Process_10
B	Process_11	How2Process_11

• Table Name - Train
• Named Ranges Products, Processes, Topics

User Select Section

sProducts (column A)	sProcesses (column B)	sTopics (column C)
<uses a normal data-validation to select unique values from the Products column> Drop-Down	<uses data validation to select the Processes where Products drop-down= a value (A or B in this instance> Drop-down	<uses data validation to select the Topics where Processes=a value and where Products = a value> Drop Down
Data validation	Data validation	Data validation
Data validation	Data validation	Data validation
Data validation	Data validation	Data validation
(Duplicate values)	(Duplicate values)	(441 unique values in the raw data)

I can't use the named ranges Processes, Products, or Topics directly in the data validation - it returns errors.
The solution was to create three more columns and spill the values from the Named Ranges: Products, Processes, Topics. From there, create data validations with offset within the Data Validation for each column.

Look-up values sorrted out.

Now the hard part that I cannot seem to figure out.
Based on the Data Table Train, when the user has selected sProducts as A, then the only options for sProcesses are those associated with sProducts. This logic should carry across to sTopics too.
(note: the real raw data has repeated values in sProducts and sProcesses. the ultimate goal is to use sProducts and sProcesses to get to the correct sTopics.)

I want to used named ranges, tables for the solution but I can seem to get the second part of the logic to work.

Each row in the User select area, will be a record and tied to other information in the worksheet such as training group, number of learners, etc., the point being all of the static solutions out there don't work in this use case. (or at least I can't get them to work).

Your wisdom will be most appreciated.

 

[MikeLevine]

as a follow-up, the data validation-drop-down lists are for each cell in this example and for the finished product.

it doesn't help that I come from a Database mindset and find myself wishing for SQL syntax on the data validation dropdowns.

 

[MikeLevine]

This has been a great exercise and I've discovered how to reference a cell generically, but not in a drop-down data validation.
The rationale being if i can refer to the sProducts cell from the sProcesses cell (both on same row) then I might be able to do it, but alas, that approach failed.

From the perspective of the user Select Section -> sProcesses and sTopics cells

=INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)): Doesn't work in Data Validation but in a cell, works perfectly =UNIQUE(FILTER(Train[Processes],Train[Products]=INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)),"")) here too so both of these are great if not needed in a Data Validation List.