Dependent drop-down lists in multiple rows
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on the adjacent value on the same row, in other words, they are dependent on each other. Select Region "East" demonstrated above and you can only select Products "BB" and "EE", the table in cell range B2:C11 determines how they are related.
Regular drop-down lists used here are easy to insert and customize, the formulas in this workbook are easy to create and so are the named ranges as well. I will in detail explain how they work.
The downside with this approach is that you can only use two connected drop-down lists. For example, three dependent drop-down lists actually require a three-dimensional table which is possible using VBA, however, outside the scope of this article. Note, to be clear, there is no VBA code in the example demonstrated here.
Another downside with regular drop-down lists is that if you select a Region value and then a Product value and then go back and change the Region value to another value the Product value stays the same. It would have been better if that value became blank in those occasions.
It is also possible to paste a value and overwrite the drop-down list, this is how they are designed and nothing I can do about more than using perhaps VBA code or Event code to prevent such scenarios.
Check out the Dependent Drop Down lists Add-In if you need more than two dependent drop-down lists.
How I built this workbook
Cell range B2:C11 contains an Excel defined Table that automatically expands or shrinks when values are added or deleted respectively, you don't need to adjust cell references each time you edit the Excel defined Table which is a huge time saver.
There is also another data set, shown below, that is built using two array formulas. The data set extracts items (Products) for each possible value (Region) which makes it easier to populate the drop-down lists using only formulas and named ranges.
This table is dynamic and changes instantly whenever the source table is edited. Don't worry, I will explain the table above and the formulas used later in this article.
Excel defined Table
Here is how to create an Excel defined Table:
- Select cell range B2:C11.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Table" button.
- Press with left mouse button on the checkbox "My table has headers" if your data set has headers.
- Press with left mouse button on OK button.
The main reason I am using an Excel defined Table here is that you can use a "structured reference" which is basically a table name instead of a regular cell reference.
Relationships between categories
The first array formula returns unique distinct values in cell H2:M2 from named range Region. The second array formula in cell range H3:M7 returns coherent values from each region. All of these calculations can be hidden or placed on a calculation sheet.
Array formula in cell H2:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Array formula in cell H3:
Copy cell H3 and paste to cell range H3:M8, if you have more categories (Regions) and Items (Products) than this example you may have to extend the formulas to a larger cell range accordingly.
Named ranges
Now it is time to create the two last named ranges that will be used in data validation lists.
- Select cell F3, this step is very important because the named ranges contain formulas that contain relative cell ranges.
- Go to tab "Formula" on the ribbon.
- Press with left mouse button on the "Name Manager" button and a dialog box appears.
- Press with left mouse button on "New..." button.
- Enter the name UniqueRegion shown below.
- Enter the formula.
- Press with left mouse button on OK button.
- Repeat step 3 to 6 with UniqueProduct
Name: UniqueRegion
Formula:
Name: UniqueProduct
Formula:
You can find an explanation to the dynamic named ranges displayed above in the article below.
Recommended articles
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
Data validation lists
The following steps shows you how to create regular drop-down lists in column E, linked to a dynamic named range UniqueRegion.
- Select cell E3
- Go to tab "Data"
- Press with left mouse button on "Data validation" button
- Select "List"
- Type =UniqueRegion
- Press with left mouse button on OK
- Copy cell E3 and paste to cells below as far as needed.
The following steps shows you how to create regular drop-down lists in column F, linked to a dynamic named range UniqueProduct.
- Select cell F3
- Go to tab "Data"
- Press with left mouse button on "Data validation" button
- Select "List"
- Type =UniqueProduct
- Press with left mouse button on OK
- Copy cell F3 and paste to cells below as far as needed.
Dependent drop down lists category
Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Excel categories
29 Responses to “Dependent drop-down lists in multiple rows”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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.
VBA Example ?
Why you used 'Dependent values' what it will do. can we add delete this, will it work or not??? kindly let me know.
I don't want to use 'Dependent values'! Words in formula. But I can't. We don't use the another sheet, so why we use to urgency "!" sembol with sheet name?
I can't understand why we are matching with zzzzzzzz. I don't want to use match. Could you do this example with vlookup please. Thank you.
Need help with data similar to file Sukhdev.xlsx. Three dependent columns; first to have equipment type, second to have equipment brand and third to have the equipment model. I'm thinking I should have 2 sets of caluclation sheets similar to the one in tutorial. Is there an easier way?
Liju A,
I am not sure how, if you want to use array formulas.
My Dependent drop down list Add-In handles more than 2 columns easily.
or check out: https://www.contextures.com/xlDataVal13.html
Normally I don't learn article on blogs, but I would like to say that this write-up very compelled
me to check out and do it! Your writing taste has been surprised me.
Thanks, very great article.
hello everyone!
i'm trying to use a combo box filtered results based on a matching criteria between two sheets. please see example below.
Sheet1
A2(1/1)
A3(1/2
a4(1/3)
a5(1/4)
a6(1/5)
all the way to a99 with value (1/98) without repeating values on column A, unique values on sheet1!a:a
Sheet2
a2(1/1
a3(1/1)
a4(1/2)
a5(1/3)
a6(1/4)
a7(1/4)
a8(1/4)
and as you can see column a:a may have multiple concurrent rows with same value. repeating values on sheet2!a:a, these match sheet1!a:a
row number may go up as high as needed depending on what is needed to list from 1/1 to 1/98.
sheet2
b2(300)
b3(210)
b4(210)
b5(215)
b6(230)
b7(215)
b8(300)
repleating value on sheet2!b:b
so what i want is on sheet1 column B with combo box that will give me the to choose from list of values as example below
b2 combo box value options of 300 or 210
b3 combo box value option of 210
b4 combo box value option of 210
b5 combo box value options of 215
b6 combo box value options of 230, 215 and 300
etc all the way down to b99 matching rowcount with column a
sheet1!a:a is already filled with unique non repeating (1/x) values. these are the values tha repeat on sheet2!a:a
sheet3!a:a is already filled with unique no repeating (###) values. these are the values that repeat on sheet2!b:b
thank you in advance for your assistance and suggestions. i welcome your questions.
regards,
komdoree
Uniqueproduct isnt working.
Isn't there any simpler way of doing this?
Hi, I'm liking your tutorials, thanks for the work!
That said, it seems a little silly to create a dynamic range (as in one of your other examples) or have an automatically expanding range when you have to manually copy formula cells into a table. Is there any way to have that table automatically expand or to do this without having a reference table?
Thanks,
Hi Oscar,
Thanks for the posts.
My product list is not duplicate. Also When I am trying to create unique product in another sheet I am getting error.
Regards,
Naveen N
This is an interesting.
I don't like unique data is sorted by each Column.
I tried resorting it as By Row and have it still working but I keep failing. Where am I going wrong?
I changed all Row formula to Column and Row to Column to accommodate this changed.
For example
a - =INDEX(Region,MATCH(0,COUNTIF($F$1:F1,Region),0))
a2 - =INDEX(Region,MATCH(0,COUNTIF($G$10:G10,Region),0))
b - =INDEX(Product,SMALL(IF(G$1=Region,MATCH(ROW(Region),ROW(Region))),ROW(A1)))
b2 - =INDEX(Product,SMALL(IF($G12=Region,MATCH(ROW(Region),ROW(Region))),COLUMN(A2)))
Product and Region is left intact. I don't change.
=OFFSET('Dependent values'!$H$2,MATCH('Dependent values'!$D2,'Dependent values'!$G$1:$G$7,0)-1,0,0,SUMPRODUCT(--('Dependent values'!$D2=Region)))
to
=OFFSET('Dependent values'!$H$12,MATCH('Dependent values'!$D2,'Dependent values'!$G$11:$G$16,0)-1,0,0,SUMPRODUCT(--('Dependent values'!$D2=Region)))
I have attached the file I use for reference.
https://www85.zippyshare.com/v/llqyWOyy/file.html
Thank you.