Dependent data validation lists in multiple rows
This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent column. There is no vba code in the example demonstrated here.
Named ranges
The following named ranges expands automatically when new text values are added.
Name: Product
Formula:
Name: Region
Formula:
Array fomulas
The first array formula returns unique distinct values in cell G1:L1 from named range Region. The second array formula in cell range G2:L7 returns coherent values from each region. All of these calculations can be hidden or placed on a calculation sheet.
Array formula in cell G1:
Array formula in cell G2:
How to create an array formula
- Select a cell
- Type array formula
- Press and hold Ctrl + Shift
- Press Enter
Copy cell G1 and paste to cell range H1:L1. Copy cell G2 and paste to cell range G2:L7.
Named ranges
Now it is time to create the two last named ranges that will be used in data validation lists.
Name: UniqueRegion
Formula:
Name: UniqueProduct
Formula:
Data validation lists
- Select cell range D2:D9
- Go to tab "Data"
- Click "Data validation" button
- Select "List"
- Type =UniqueRegion
- Click OK
Repeat above steps with cell range E2:E9 and Source: =UniqueProduct.
Download excel *.xlsx file
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
February 22nd, 2012 at 12:09 pm
Oscar instead of using "ZZZZZ", use a "Ω" to the same effect. Applicable only for finding the last Text.
February 22nd, 2012 at 10:33 pm
chrisham,
thanks!
March 21st, 2012 at 5:59 pm
Why did you choose 'Dependent values'!$D13 for the UniqueProduct formula?
March 28th, 2012 at 2:18 pm
Paulo,
You found an error, I have uploaded a new file and made some corrections to this post.
Thanks for bringing this to my attention!
April 23rd, 2012 at 4:13 am
Hi Oscar,
What about my data list of the Product have duplicated value and I only want to show unique value?
=INDEX(Product, SMALL(IF(G$1=Region, MATCH(ROW(Region), ROW(Region))), ROW(A1)))
Also, how can I make a list to match 2 value?
Here is my data sample ( My lst is huge!)
Division Dept Line Subline
Women D121 Tops T-shirts
Women D121 Bottom Plants
Women D123 Tops Jacket
Women D121 Tops Jacket
Mens D124 Bottom Plants
Mens D124 Bottom Underwear
Mens D125 Bottom T-shirts
..............................
Before, I use vba to write the data validation. However as the list is too large. The excel sometimes become no responding and take times to write the unique list.
Thank you!!!!!
April 25th, 2012 at 10:16 am
Penny,
Before, I use vba to write the data validation. However as the list is too large. The excel sometimes become no responding and take times to write the unique list.
I don´t think a formula is faster for filtering unique distinct values. I would suggest using vba and pivot table:
Excel vba: Populate a combobox with values from a pivot table
Change pivot table data source using a drop down list
Dependent Drop Down Lists AddIn