Multi-LEVEL Dependent Drop-down lISTs Hi-res PDF, Video Example File: https://bit.

ly/ddlists

Hi-res PDF, Video & Example File: https://bit.ly/ddlists

These may also be known as

1 Dynamic Dependent Drop-down Lists ‘cascading drop-down lists’. 2 Multi-level Dependent Drop-down Lists

Step 1 : Define Name for First Level: Formulas tab > Define Name
Step 1 : Add Data Functions Used

UNIQUE Masterclass: https://bit.ly/da-unique

SORT Masterclass: https://bit.ly/da-sort

XLOOKUP Masterclass: https://bit.ly/fnxlookup

Functions Used
Add data in an TRANSPOSE Masterclass: https://bit.ly/fntranspose
Excel table & give CELL Masterclass: https://bit.ly/fncell
your table a name
FILTERMasterclass:
FILTER Masterclass: https://bit.ly/filterfunc
https://bit.ly/filterfunc
INDIRECT Masterclass: https://bit.ly/fxindirect

Select the first

Step 2 : Shape Data level column FILTER Masterclass: https://bit.ly/filterfunc
from your data
Use the TRANSPOSE + SORT+
UNIQUE function combination to
list down level 1 dropdown values
Step 2 : Create level 1 data validation dropdown
Benefits:
1. No need to anticipate the
maximum number of
level 1 values.
Use FILTER function to list
dependent column data under 2. Supports more than 2
each level 1 heading levels of dependencies

Reference the
Limitations:
named range
Copy FILTER formula across 1. Requires editing level 1
defined in step 1
columns to allow for more
dropdown, every time
countries to be added in future.
you need to edit the
dependent dropdown

Step 3 : Create Dropdowns: Data Tab > Validation

IMPORTANT
This technique exploits the CELL
function’s ability to return the
reference to the last edited cell.
Therefore, the level upstream
from the drop-down you want to
select from must be the last
edited cell. Download the file for
an alternate solution.

Create level 1 data validation dropdown. Step 3 : Create Dependent data validation dropdown
# Operator ensures that new values get
dynamically added to the dropdown

For more levels: copy the

Use INDIRECT + CELL function
formula & modify the
combination to filter out
references accordingly
regions for the last edited cell

Limitations:
GET MORE TIPS
Use XLOOKUP to create dependent 1. Requires anticipation of the Create the data validation list
+
data validation dropdown
maximum number of countries. step-by-step
2. Supports only 1 dependent level video
https://bit.ly/filterfunc

Mynda Treacy
TEACHING YOU CAREER TRANSFORMING SKILLS Follow me for tips and tutorials

Follow me for tips and tutorials © Copyright 2024