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
Dependent-Data-validation-lists-in-multiple-rows1.xlsx
Related posts:
Apply data validation lists dynamically (vba)
Create dependent drop down lists containing unique distinct values in multiple rows
How to use a table name in data validation lists and conditional formatting formulas
Recommended categories
Oscar instead of using "ZZZZZ", use a "Ω" to the same effect. Applicable only for finding the last Text.
chrisham,
thanks!
Why did you choose 'Dependent values'!$D13 for the UniqueProduct formula?
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!
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!!!!!
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
Why you used "zzzzzz" what it will do. can we add "sukhdev" in place of "zzzzzz". will it work or not??? kindly let me know.
Sukhdev,
MATCH("ZZZZZZZZ", 'Dependent values'!$A:$A)-1 returns the last cell row containing a text string. You could use this value: ÿ instead to make sure all text strings are matched.
What should we use for Numbers (code) and text both. Please help me.
Sukhdev,
Try this:
=MAX(MATCH("ÿ",'Dependent values'!$A:$A),MATCH(9E+307,'Dependent values'!$A:$A))-1
Hi Oscar Thanks for reply
I tried the same in Name manager but did get success. I Used below formula in Name Manager;
=MAX(MATCH("y",Sheet1!$A:$A),MATCH(9E+307,Sheet1!$A:$A))-1 (for Column A)
=MAX(MATCH("y",Sheet1!$B:$B),MATCH(9E+307,Sheet1!$B:$B))-1 (for Column B)
and My data is below.
Material Group Material Name
1156 Mouse Optical Neo Usb
1187 UPS Black Armour 725
1187 UPS Black Armour 725
1112 COMPUTER M/M SPEAKER BEATS IT-1875 SUF R
1111 Computer Cabinet P4 Entizer W Smps & Usb
1111 Computer Cabinet P4 Supernova RB w smps
1111 Computer Cabinet P4 Orion RB w smps & us
1111 Computer Cabinet P4 Empower- R w smps &
1196 Data Card Speed-3Gv 7.2”(Modem)
1112 Computer Multimedia Speaker IT 2000 Sb J
Sukhdev,
No, I just pointed out the part of the formula that returned the error. Here is the whole formula:
=OFFSET(Sheet1!$A$2, 0, 0, MAX(MATCH("ÿ",Sheet1!$A:$A),MATCH(9E+307,Sheet1!$A:$A))-1)
See this file:
Sukhdev.xlsx
Hi Oscar,
Above mentioned Formula is working perfectly. Thanks a lot for helping me.
Thanks,
Best Regards,
Sukhdev
Sukhdev,
I am happy you got it working!
Hi Oscar
I have two columns Mcode and Mname and I wish to make dynamic drop down of unique Mcode. If I select One M code, all the Mname including duplicate under that mcode should reflect. My data is very huge so I want to create it dynamically. Please help me.
Thanks,
Regards
Sukhdev
Hi Oscar
Do you have a tutorial or example to do this exact same thing but using VBA?
Mike
Mike,
Sorry no.