Data validation to restrict possible cell values based on filter

Question

I have a table of values: capabilities, domains, sub-domains and descriptions (B2:E322).

I would like to perform data validation similar to that conducted by the standard filter selection - if I filtered the table for one of the capabilities, only the mapped domains are now available to me in the table. My ask specifically:

• I select a domain in Sheet 2 cell B2 (I have already restricted possible entries using data validation). e.g, 'X'.
• The only options available as a drop down for selection in C2 are domains whose capability is the selected one in B2. In this example, that would be 1 and 3.
• Repeated process for sub-subdomains and the value in C2 - if 1 is the selected domain, only 'red' would be available as a sub-domain choice in d2.

Table for reference:

Capability	Domain	Sub-domain
X	1	Red
X	3	Green
Y	7	Yellow
Z	5	Blue
Z	11	Purple

I have attempted to mash the formula shown in this answer (Excel data validation based on two cell values) with little success, any assistance would be appreciated.

Answer 1

Try:

---

Helpers:

Formula in I2:

=FILTER(B2:B6,A2:A6=F2,"")

Formula in J2:

=FILTER(C2:C6,(A2:A6=F2)*(B2:B6=F3),"")

---

Lists:

Data - List reference in F2:

=$A$2:$A$6

Data - List reference in F3:

=I2#

Data - List reference in F4:

=J2#