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 (
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#