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

MikeLevine

New Member
Joined
Apr 4, 2002
Messages
7
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)
AProcess_1How2Process_1
AProcess_2How2Process_2
BProcess_10How2Process_10
BProcess_11How2Process_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 validationData validationData validation
Data validationData validationData validation
Data validationData validationData validation
(Duplicate values)(Duplicate values)(441 unique values in the raw data)

1594742423725.png

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.

1594744408028.png
1594744600889.png
1594744688459.png

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.
 

Attachments

  • 1594742061071.png
    1594742061071.png
    65.4 KB · Views: 0
  • 1594744542839.png
    1594744542839.png
    11.4 KB · Views: 0
  • 1594744665883.png
    1594744665883.png
    11.3 KB · Views: 0

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

MikeLevine

New Member
Joined
Apr 4, 2002
Messages
7
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

New Member
Joined
Apr 4, 2002
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,187
Messages
5,570,752
Members
412,340
Latest member
nikitesh95
Top