Making a cascading (dependent) Excel drop down list
Last week we started to explore the capabilities of Excel Data Validation and learned how to create a simple drop-down list in Excel based on a comma-separated list, range of cells or a named range.
Today, we are going to investigate this feature in-depth and learn how to create cascading drop down lists that display choices depending on the value selected in first dropdown. To put it differently, we will make an Excel data validation list based on the value of another list.
How to create a simple cascading dropdown in Excel
Making simple dependent drop-down lists in Excel is easy. All you need is a few named ranges and the INDIRECT formula. This method works with all versions of Excel 2019, 2016, 2013, 2010 and earlier.
1. Type the entries for the drop-down lists.
First off, type the entries you want to appear in the drop-down lists, each list in a separate column. For example, I'm creating a cascading dropdown of fruit exporters and column A of my source sheet (Fruit) includes the items of the first dropdown and 3 other columns list the items for the dependent dropdowns.
2. Create named ranges.
Now you need to create names for your main list and for each of the dependent lists. You can do this either by adding a new name in the Name Manager window (Formulas tab > Name Manager > New) or typing the name directly in the Name Box.
For the detailed step-by-step instructions please see Creating a named range.
Things to remember:
- The items to appear in the first drop-down list must be one-word entries, e.g. Apricot, Mango, Oranges. If you have items consisting of two, three or more words, please see How to create a cascading dropdown with multi-word entries.
- The names of the dependent lists must be exactly the same as the matching entry in main list. For example, the dependent list to be displayed when "Mango" is selected from the first drop-down list should be named Mango.
When done, you may want to press Ctrl+F3 to open the Name Manager window and check if all of the lists have correct names and references.
3. Make the first (main) drop-down list.
- In the same or in another spreadsheet, select a cell or several cells in which you want your primary drop-down list to appear.
- Go to the Data tab, click Data Validation and set up a drop-down list based on a named range in the usual way by selecting List under Allow and entering the range name in the Source box.
For the detailed steps, please see Making a drop down list based on a named range.
As the result, you will have a drop-down menu in your worksheet similar to this:
4. Create the dependent drop-down list.
Select a cell(s) for your dependent drop-down menu and apply Excel Data Validation again as described in the previous step. But this time, instead of the range's name, you enter the following formula in the Source field:
=INDIRECT(A2)
Where A2 is the cell with your first (primary) drop-down list.
If cell A2 is currently empty, you will get the error message "The Source currently evaluates to an error. Do you want to continue?"
Safely click Yes, and as soon as you select an item from the first drop-down menu, you will see the entries corresponding to it in the second, dependent, drop-down list.
5. Add a third dependent drop-down list (optional)
If needed, you could add a 3rd cascading drop-down list that depends either on the selection in the 2nd drop-down menu or on the selections in the first two dropdowns.
Set up a 3rd dropdown that depends on the 2nd list
You can make the drop-down list of this type in the same fashion as we've just made a second dependent drop-down menu. Just remember the 2 important things discussed above, which are essential for the correct work of your cascading drop-down lists.
For instance, if you want to display a list of regions in column C depending on which country is selected in column B, you create a list of regions for each country and name it after the country's name, exactly as the country appears in second dropdown lists. For instance, a list of Indian regions should be named "India", a list of Chines regions - "China", and so on.
After that, you select a cell for the 3rd dropdown (C2 in our case) and apply Excel Data Validation with the following formula (B2 is the cell with the second drop-down menu that contains a list of countries):
=INDIRECT(B2)
Now, each time you select India under the list of countries in column B, you will have the following choices in the third drop-down:
Create a third dropdown dependent on the first two lists
If you need to create a cascading drop down menu that depends on the selections both in the first and second drop-down lists, then proceed in this way:
- Create additional sets of named ranges, and name them for the word combinations in your first two dropdowns. For example, you have Mango, Oranges, etc. in the 1st list and India, Brazil, etc. in the 2nd. Then you create named ranges MangoIndia, MangoBrazil, OrangesIndia, OrangesBrazil, etc. These names should not contain underscores or any other additional characters.
- Apply Excel Data Validation with the INDIRECT / SUBSTITUTE formula that concatenates the names of the entries in the first two columns, and removes the spaces from the names. For example, in cell C2, the data validation formula would be:
=INDIRECT(SUBSTITUTE(A2&B2," ",""))Where A2 and B2 contain the first and second dropdowns, respectively.
As the result, your 3rd drop-down list will display the regions corresponding to the Fruit and Country selected in the first 2 drop-down lists.
This is the easiest way to create cascading drop-down boxes in Excel. However, this method has a number of limitations.
Limitations of this approach:
- The items in your primary drop-down list must be one-word entries. See how to create cascading drop-down lists with multi-word entries.
- This method won't work if the entries in your main drop-down list contain characters not allowed in range names, such as the hyphen (-), ampersand (&), etc. The solution is to create a dynamic cascading dropdown that does not have this restriction.
- Drop-down menus created in this way are not updated automatically i.e. you will have to change the named ranges' references every time you add or remove items in the source lists. To get over this limitation, try making a dynamic cascading drop down list.
How to create cascading drop-down lists with multi-word entries
The INDIRECT formulas like we used in the example above can handle one-word items only. For example, the formula =INDIRECT(A2) indirectly references cell A2 and displays the named range exactly with the same name as is in the referenced cell. However, spaces are not allowed in Excel names, which is why this formula won't work with multi-word names.
The solution is to use the INDIRECT function in combination with SUBSTITUTE like we did when creating a 3rd dropdown.
Suppose you have Water melon among the products. In this case, you name a list of water melon exporters with one word without spaces - Watermelon.
Then, for the second dropdown, apply Excel Data Validation with the following formula that removes the spaces from the name in cell A2:
=INDIRECT(SUBSTITUTE(A2," ",""))
How to prevent changes in the primary drop down list
Imagine the following scenario. Your user has made the selections in all of the drop-down lists, then they changed their mind, went back to the first list, and chose another item. As the result, the 1st and 2nd selections are mismatched. To prevent this from happening, you may want to block any changes in the first drop-down list as soon as a selection is made in the second list.
To do this, when creating the first dropdown, use a special formula that will check whether any entry is selected in the second drop down menu:
=IF(B2="", Fruit, INDIRECT("FakeList"))
Where B2 contains the second dropdown, "Fruit" is the name of the list that appears in the first drop-down menu, and "FakeList" is any fake name that does not exist.
Now, if any item is selected in the 2nd drop-down list, no choices will be available when the user clicks on the arrow next to the first list.
Creating dynamic cascading drop-down lists in Excel
The main advantage of a dynamic Excel dependent drop-down list is that you are free to edit the source lists and your drop-down boxes will get updated on the fly. Of course, creating dynamic dropdowns requires a bit more time and more complex formulas, but I believe this is a worthy investment because once set up, such drop-down menus are real pleasure to work with.
As with almost anything in Excel, you can achieve the same result in several ways. In particular, you can create a dynamic dropdown using a combination of OFFSET, INDIRECT and COUNTA functions or a more resilient INDEX / MATCH. The latter is my preferred way because it provides numerous advantages, the most essential of which are:
- You have to create 3 named ranges only, no matter how many items there are in the main and dependent lists.
- Your lists may contain multi-word items and any special chars.
- The number of entries can vary in each column.
- The entries' sort order does not matter.
- Finally, it's very easy to maintain and modify the source lists.
Okay, enough theory, let's get to practice.
1. Organize your source data in a table.
As usual, the first thing for you to do is to write down all the choices for your drop-down lists in a worksheet. This time, you will have store the source data in an Excel table. For this, once you have entered the data, select all of the entries and press Ctrl + T or click Insert tab > Table. Then type a name of your table in the Table Name box.
The most convenient and visual approach is to store the items for the first drop-down as table headers, and the items for the dependent dropdown as table data. The screenshot below illustrates the structure of my table, named exporters_tbl - the fruit names are table headers and a list of exporting countries is added under the corresponding fruit name.
2. Create Excel names.
Now that your source data is ready, it's time to set up named references that will dynamically retrieve the correct list from your table.
2.1. Add a name for the table's header row (main dropdown)
To create a new name that references the table header, select it and then either click Formulas > Name Manager > New or press Ctrl + F3.
Microsoft Excel will use the built-in table reference system to create the name of the table_name[#Headers] pattern.
Give it some meaningful and easy to remember name, e.g. fruit_list, and click OK.
2.2. Create a name for the cell containing the first drop-down list.
I know that you don't have any dropdown yet :) But you have to choose the cell to host your first dropdown and create a name for that cell now because you will need to include this name in the third name's reference.
For example, my first drop-down box is reside in cell B1 on Sheet 2, so I create a name for it, something simple and self-explanatory like fruit:
Please be sure to read the following few paragraphs carefully because this a very useful tip you that don't want to miss. Thanks a lot to Karen for posting it!
If you plan to copy your drop-down lists to other cells, then use mixed cell references when creating the name for the cell(s) with your first drop-down list.
For the drop-downs to copy correctly to other columns (i.e. to the right in the worksheet), use relative column (without the $ sign) and absolute row (with $) references like = Sheet2!B$1.
As the result, B1's dependent drop down list will appear in cell B2; C1's dependent drop-down will display in C2, and so on.
And if you plan to copy the dropdowns to other rows (i.e. down to other cells in the column), then use absolute column (with $) and relative row (without $) references like = Sheet2!$B1.
2.3. Create a name to retrieve the dependent menu's entries.
Instead of setting up unique names for each of the dependent lists like we did in the previous example, we are going to create one named formula that is not assigned to any particular cell or a range of cells. It will retrieve the correct list of entries for the second dropdown depending on which selection is made in the first drop-down list. The main benefit of using this formula is that you won't have to create new names as you add new entries to the first drop-down list - one named formula covers them all.
You create a new Excel name in the usual way (Formulas > Name Manager > New) with this formula:
=INDEX(exporters_tbl,,MATCH(fruit,fruit_list,0))
Where:
exporters_tbl- the name of the table (created in step 1);fruit- the name of the cell containing the first drop-down list (created in step 2.2);fruit_list- the name referencing the table's header row (created in step 2.1).
I gave it a name exporters_list, as you see in the screenshot below.
If you are curious to learn the Index and Match functions in-depth, check out this tutorial: INDEX & MATCH - a better alternative to VLOOKUP.
Well, you have already done the major part of the work! Before getting to the final step, it may be a good idea to open the Name Manager (Ctrl + F3) and verify the names and references:
3. Set up Excel Data Validation
This is actually the easiest part. With the two named formulas in place, you set up Data Validation in the usual way (Data tab > Data validation).
- For the first drop-down list, in the Source box, enter =fruit_list (the name created in step 2.1).
- For the dependent drop-down list, enter =exporters_list (the name created in step 2.3).
Done! Your dynamic cascading drop-down menu is accomplished and will update automatically reflecting the changes you've made to the source table.
This dynamic Excel dropdown, perfect in all other respects, has one shortcoming - if the columns of your source table contain a different number of items, the blank rows will appear in your menu like this:
Exclude blank rows from the dynamic cascading dropdown
If you want to clean any blank lines in your drop-down boxes, you will have to take a step further and improve the INDEX / MATCH formula used to create the dependent dynamic drop-down list.
The idea is to use 2 INDEX functions, where the first gets the upper-left cell and the second returns the lower-right cell of the range, or the OFFSET function with nested INDEX and COUNTA. The detailed steps follow below:
1. Create two additional names.
Not to make the formula too bulky, create a couple of helper names with the following simple formulas first:
- A name called col_num to reference the selected column number:
=MATCH(fruit,fruit_list,0) - A name called entire_col to reference the selected column (not the column's number, but the entire column):
=INDEX(exporters_tbl,,col_num)
In the above formulas, exporters_tbl is your source table's name, fruit is the name of the cell containing the first dropdown, and fruit_list is the name referencing the table's header row.
2. Create the named reference for the dependent dropdown.
Next, utilize either of the below formulas to create a new name (let's call it exporters_list2) to be used with the dependent drop-down list:
=INDEX(exporters_tbl,1,col_num) : INDEX(exporters_tbl, COUNTA(entire_col), col_num)
=OFFSET(INDEX(exporters_tbl,1,col_num),0,0,COUNTA(entire_col))
3. Apply Data Validation.
Finally, select the cell containing the dependent dropdown and apply Data Validation by entering = exporters_list2 (the name created in the previous step)in the Source box.
The screenshot below shows the resulting dynamic drop-down menu in Excel where all blank lines are gone!
This is how you create an Excel data validation list based on the values of another list. Feel free to download our sample workbooks to see the cascading drop-down lists in action:
If you have any questions, you are most welcome to post a comment. Thank you for reading!
You may also be interested in
231 responses to "Making a cascading (dependent) Excel drop down list"
This tutorial helped me a lot... Thank you!
I have an issue regarding cascading excel dropdown lists that I'm looking forward to solve it as soon as possible...
It is the following,
I created a cascading dropdown list with several dependent entries.
Therefore, I developed a macro to clear contents when updating my ‘mother’ cell. The thing is, I did not want a macro to clear contents, but one to update them, giving me a value instead of a blank cell. I would like to keep the dropdown option in the case I want to change my standard value.
I’m looking forward for someone how could explain it.
Kind Regards,
Fábio Pereira
Portugal
Hi Fabio,
I would like to use a macro like the one you have created. So if I change a selection in a parent menu, I want the following menus to clear.
Could you advise me how you did this ?
Many thanks
Chris
Can you create an indirect function where if you have a list with three items, say, "fruits", "trees" and "other". You want the dropdown list to appear only for "fruits" and "trees". However, once you choose "other" from the list, I want to be able to enter my own text.
Is this possible? I need a formula or another method rather than indirect function. Please assist.
Hi
I already have a multiple cascading drop down set.
I need a second non dependant set from the first but cant seem to get it to work....is it possible to have two independence, dependant cascading sets?
Set A of drop down relates to cats, Set B relates to dogs
Hello,
I have a question, I have a dropbox with 2 options. Let's say A and B,
when I select option B I want to apear one table with prices. And option A have already it diferent table in the main excel sheet.
I tried but I could not do it.
Please help!!! ASAP
Thanx
Dependent drop down is not working for multiple sheets. Please help
I have a long drop down list. Is there a way where typing the first letter will start filtering the drop down list i.e. typing "a" will show only list items starting with "a", continuing typing like "ac" will only show list items starting with "ac" and so on?
Hello,
I apply the same way and it works for only different values.
the problem is I have 3 dependent drop down lists
the first list contains unique names but the second one has repeated words for each name in the first list and the 3 only depend on them.
Its not working if Named Range values are having space. Deleting the spaces its working..Is there any way to achieve this???
I followed the instructions in your example exactly for the dynamic cascading list. However when I select a fruit, no dropdown list appears for exporters. I click on the dropdown button and no dropdown list appears. I can't see anything that I've done wrong. I copied and pasted the name from name manager just to be sure I didn't type it wrong. Any suggestions of likely errors?
Hi,
If i want to create a sheet where i need to use the cascading dropdown in the consecutive rows, how can I implement it?
For example, my first row i will select a value from dropdown list In cell A2 and dependent drop down is listed in B2.If I have implement the same in A3 and B3 how should I do it?
How to use the same formula in consecutive rows?
Hi Svetlana,
I'm trying to load a dropdown list based on doing a VLOOKUP in a data table. For example I load a data table with Orders and Items (only 1 database call) where an Order may have multiple Items.
I want the user to enter an Order # in a cell, then the corresponding Items are loaded into the dropdown list. I presume I want to load the dropdown using some form of VLOOKUP or Index/Match but can't seem to find a solution. Any help is appreciated!
thanks for your good site
how can i copy a dropdown cell to another cells of a column?
my mean is data validation-list-indirect(A2)
i want copy this in all cells of the column
Remove the last $ in your formula.
Sample:
From $B$2 to $B2
hope this helps.
Can anyone help please. I need to create 2 pick lists in excel 2010 for translation service as follows:
select language from (pick list) and select language to (pick list) and the third box should show me a list of translators based on the first 2 selections from the list only. Thanks Same
Svetlana -
Thank you so much for this great tutorial!
In case you followed the directions to the letter and it's still not working, you may have made the same silly mistake I did:
=INDIRECT(A2) refers to the cell in which the first drop down list is located, not the named range. So when you're putting in the second drop down list, you're referencing the cell preceding it. I was referencing the list as it appeared in my reference tab. Silly!
Hi have a hazard library with 500 hazard which I want to pick in a list and then I have contributing factors, controls, risks, description which I want to cascade I have only managed to be able to select the hazard and the second column contributoing factors in one cell I can't copy down and I cannot get it to copy across for the varying lists. Any ideas??
Thanks
Hi, Is there a way to reference a single primary drop down list....twice? For example if your primary drop down selection is named "vehicle" and one of those selections is "bicycle" then when you select "bicycle" there may be reason to have two non-dependent drop downs like "derailleur type" and "tire size" that would both populate when "bicycle" is selected. The problem I run into is that a named range must be unique...I can only use the primary drop down name once only.
Thanks
Hi, this post is really awesome. I have 2 questions:
1. Is there a way to reference the INDIRECT formula to another workbook? This means all my data lists for the dropdown boxes are in a different workbook. Is this possible?
2. For the dynamic dropdown with no blanks, is there a way to make it work when the cells are not truly blank? Meaning, they have formulas in them but do not return results so there's no display in the cell but it's not technically blank.
I've been stuck with these for days.
THANK YOU SO MUCH!!!
Hello, Kai,
1. yes, there's a way to reference another workbook for dropdown lists. Please, look at these example pics;
2. replace Named range counta(entire_col) with ROWS(entire_col)-COUNTBLANK(entire_col) in your formulas.
For more details go here
Course| Trainer|Date|Start time|End Time|Duration|Outcome|Notes
for example the above are rows, is it possible if i select course and it give me all the details horizontally? for example In the Course section I select Employee training, and it give me trainer name, date of training, start time etc?
Hi terry, Please help me, In datavalidation dropdown list which is open by click on mouse but I want to open dropdown list when we select, datavalidation list shouldbe show the option, thank you.
How do you create a dependent drop down list for text with space and character such as "/" or "&"?
Hi!
Thanks for the usefull information. This explanation is awesome when using just one dependent dropdown. However, I'm working on a sheet with multiple dependent dropdown (B1 is dependent on A1, C1 is dependent on A1 and B1, D1 is dependent on A1, B1 and C1,....
Any advice on how I should do this?
Thanks a lot!
I have successfully used the example for Dynamic dropdown menus (thank you) and I would like to know if is possible to adapt to fill-in a table with X-Y range of cells where the selection may applied for for any of these cells. So far I can only make it working for rows or columns (take off the "$") in the reference cell setup (fruit in your example).
Many thanks
Hello,
I have successfully created multiple drop down lists for 12 different categories each having 5 options/choices. I used an index-match function to create scores for each category based on what was chosen and then added all categories to receive a total score. My problem is I can't find a of being able to have another drop down list with the customer name where when you select any customer their scores and selections in the other drop down lists would automatically appear/save.
Thanks!
I have two drop down lists in two different sheets of the same workbook. When I change the value in one dropdown, the other dropdown also should display the same value in the second sheet and when I change value in the second sheet the first one also should change to the same value. Both of the lists contain the same values.How can I do that? Would you help me please? VBA macro would be better for me.
What would you do if your "fruit" was listed in the first column of your table instead of your table headers?
For example:
Fruit: Country:
Apricot Algeria
Apricot Iran
Apricot Pakistan
Apricot Turkey
Ive used your index match method, but placed the match function in the "rows" instead of "columns". However, my drop down list for Country that is based on my Fruit selection is only returning the 1st result. In the example above it would only return Algeria instead of a drop down list of Algeria:Turkey.
Actually, figured out a solution using a similar approach to your remove blank cells approach "=INDEX(exporters_tbl,1,col_num) : INDEX(exporters_tbl, COUNTA(entire_col), col_num)"
I use the above to return the 1st row where the chosen country appears and the last row where the chosen country appears.
Hi Kevin,
I have the same requirement as yours where i have the values in 2 columns instead of table headers; Can you please help me with the formula to be used for 2nd dropdown when i select a value in the 1st dropdown.
Hey All,
I am tryig to develop 30 lines in which the user of the excel file can put only certain predefined values. Dependent drop-down lists seem to be a workable solution, but due to structure and dynamics of the data base I cannot apply any of the formulas/codes so far. I am much obliged or any help. Here it is the back ground
I have a database in a worksheet where:
- in column A is list of all producer; and
- in column B next to the producer's name is included list with this producer's products Products are listed in column B and ocupy 3 to 7 cells from column B
- 1 row contains always only 1 cell with value inside. Either in column A is the name of the producer, or in column B is a product, which is produced by this producer.
By way of example:
- Row 1 is for headings;
- A2 - Producer1;
- B3 - Product1, B4 - Product2, B5 - Product3
- A6 - Producer2;
- B7 - ProductX, B8 - ProductY, B9 - ProductZ
etc.
Could someone please help?
how to put these conditions
E5>1 AND G5>1 THEN YES OTHERWISE NO
IF E52 THEN YES OTHERWISE NO
Hello, Tannu,
I believe the IF function together with AND can help. Please tale a look at the article explaining how they works here.
Please help!!!
I want to extract the list(data validation) from the data as below. First lookup the PO# and then create a list of corresponding distinct values. First column named as "PO#" and the second column is "Category".
PO# Category
KIPS/IT/01 CPU
KIPS/IT/01 LCD
KIPS/IT/01 LCD
KIPS/IT/02 Cable
KIPS/IT/02 CPU
KIPS/IT/02 LCD
KIPS/IT/03 Connector
KIPS/IT/04 CPU
KIPS/IT/04 LCD
KIPS/IT/04 Cable
KIPS/IT/04 Connector
KIPS/IT/04 Mouse
KIPS/IT/05 CPU
KIPS/IT/05 LCD
KIPS/IT/05 Cable
KIPS/IT/06 CPU
KIPS/IT/06 LCD
KIPS/IT/06 Cable
KIPS/IT/06 Connector
KIPS/IT/06 Mouse
KIPS/IT/07 Webcam
KIPS/IT/07 Toner
KIPS/IT/07 Miscellaneous
Hello everyone.
I am having trouble finding an error in a drop-down dependent worksheet I have created.
My worksheet is formatted so that a user would select in column B one of 6 choices. This is working as intended.
In the next column, C (to the right), a drop-down box with three different choices dependent on the selection in column B should populate. I intended for this process to be copied exactly the same way in the rows beneath.
The first cell available in my worksheet for the first choice is B8. The cell next to it with the drop-down dependent selection is C8. This is actually working.
In the rows beneath this, column B is working correctly. The error I cant find is that, say you select a different choice in column B below B8, the dependent drop-down boxes in the rest of column C only show the choices for B8's options. Its as if the entire column C is only checking B8 for the dependent drop downs.
Here are all the formulas I am using:
category_list: =INDEX(standards_tbl,,MATCH(standard,standards_list,0))
Print_Area: ='Standards Journal'!$A$1:$D$26
standard: ='Standards Journal'!$B$8
standards_list: =standards_tbl[#Headers]
standards_tbl: =Sheet2!$b$3:$G$5
I have tried updating the "standard" formula to include the entirety of column B but this doesn't seem to fix the issue.
Any ideas what I am doing wrong?
Hi,
I need some help.
Turkey is a common exporters, how could I do to add a third drop down list based on the exporters?
For eg, Mango> Turkey> northern Turkey / southern turkey
apricot> turkey> central turkey / northern turkey
such that, central turkey is not a option for mango
how and what should i do such that third drop down list is dependent on the second drop down list?
Kindly assist
Thank you
Hello,
please take a closer look at the point of the article above saying Add a third dependent drop-down list (optional). There's a tutorial with example pictures that should help :)
Hi Natalia,
I understand on the Add a third dependent drop-down list, however, my problem is 'Turkey' is a repetition. I have a issue such that, if I select Mango> Turkey> I only want 2 choice either North/South. And if I select Apricot > Turkey> I only want 2 choice either North/Central.
Because 'Turkey' is a repetition as a dependent list, I could not link the 3 dependent list
Please assist what can be done
Hi,
could you please send us a small example workbook to support@ablebits.com with the link to this comment?
Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.
We'll look into your task and try to help.
Hello Dear,
I create a drop down list of 3 steps. Which are thana, market & outlet. I could complete it 2 steps but can't complete last step, means when I click thana it shows his market what I setup but when I Click market it doesn't show his outlet. How do I solve this?
Hello,
if I understand your task correctly, it looks like you forget to create the third drop down list. Or did something wrong when creating one.
Please take a closer look at the fifth point of the article above - 5. Add a third dependent drop-down list (optional). I kindly ask you to follow the instructions described there.
If this doesn't help, I'm afraid we will need more details on your task, lists for drop down, etc.
HiSvetlana Cheusheva
You are genius. Thanx a lot for such a wonderful topic in excel. I love excel.
I thoroughly enjoyed this tutorial, thank you so much for the time and effort it surely took to create!
I did run into an issue with the "dynamic cascading dropdowns with no blanks".
When I enter the ..._list2 Source in the Data Validation window I get a message stating the Source evaluates to an error. My list uses groups and subgroups rather than fruit and exporters.
Here are the extra names I created for the no blanks formulae:
subgroup_list2 =INDEX(groups_tbl,1,col_num) : INDEX(groups_tbl,COUNTA(entire_col),col_num)
col_num =MATCH(group,group_list,0)
entire_col =INDEX(groups_tbl,,col_num)
Kind regards
Great article! Helped a lot!
Got it working, removed the spaces flanking the colon in the subgroup_list2 formula!
Great article .. good explanation and easy to follow instructions.
While the cascading dropdowns works well without VB, it has one major drawback !
One is NOT able to enter a new value from dropdown, until all sub categories( dependant dropdowns) are cleared.
However one is ABLE to delete or paste a new value, which is a real pity.
Would be nice if that loophole could be blocked.
totally awesome! Thanks a lot for this article
Thanks for this post, this is a really interesting approach. I use Excel tables and pivot tables quite often, and so a cascaded dropdown set would be most useful as the validation for a pair of table columns.
So as an example, say I had a second table to collect fruit inspections with columns:
Inspection Number | Fruit Observed | Country of Origin | Inspection Result
I'd like to put the Fruit dropdown in the Fruit Observed, and have that cascade to the Country of Origin field on the same row.
Is this feasible?
Jeff
I made for myself a simpler 2 step solution for detecting primary and secondary dropdowns that mismatch due to changing primary after secondary has been selected:
Step 1: start primary and secondary choices with matching numbers i.e., primary choices 1 - Apricot, 2 - Mango, 3 - Oranges; secondary example 2.1 - Philippines.
Step 2: Use conditional formatting to make the secondary choice cell change color (say turn bright red) if first left digit of primary not equal to first left digit of secondary, i.e. 3 - Oranges with 2.1 - Philippines, 3 ≠ 2, secondary choice cell's background turns red.
We can also use conditional cell formatting to show a glaringly different color if no choices have been made yet.
Jossy
I have a table containing first two columns having two indipendent drop-down lists. I like to have a drop-down list in the third column depending on the values of the first two cells.
Please advice.
Hi,
I have a table in which I wish to use each column to restrict the choices for the next column, which restricts the choices for the next column, and so on. A lot of the information in each column is repeated, rather than being unique values as listed here. Can you possibly help with this?
Say we have 2 Cells C7 & D7 each have a Data Validation. Well if we change the value of the Cell in the C7 then the Value of the Cell D7 should also Change according to the corresponding List of C7 or turn blank. But the value of D7 always remain the previous value of Data Validation changed in C7. We have to Change D7 manually later after changing C7. Can it be possible without VB.
Hello,
I have two questions:
-I followed the "Exclude blank rows from the dynamic cascading dropdown" instructions and I don't have blanks, but the title/header is in my second dropdown box. Is there a way to not have it?
-I am also working with 5 variables. Is there a way to reference a complete table for each dropdown? Or do I have to make independent lists for everything?
Thank you!
Hi,
How would you set this up by having attributes running on rows rather than over columns. The dropdowns need to run for new rows added.
Regards
Hey,
I was just wondering how I could add a third dynamic cascading drop-down, based on options that show up in the second drop-down.
Thanks
Thank You for the article!!
Great Article - Thanks!
This was super helpful. Thank you so much!
I am trying to create a spreadsheet that allows me to choose a Job Number and then, from the job number selected, will allow me to choose a Phase code specific to that job.
I am running into erros every time i input the =INDIRECT(A2) data validation and can't get past it.
it is very hard for me to get my Job Number to be corresponding with the Phase codes.
Hi, I have created one spreadsheet, where I am selecting employee ID and based on the employee ID all the data will get populated in other cells. I want to clear all cell value if I change the employee ID. Can any of you help me to get it done.
Thanks in advance
Hi, I am having an issue in indirect function. I have used name range method and the drop down works to pick up value from the the list. It works and drop-down list show correctist for first row. When I copy down the rows with fuction for entry, the drop-down does not give the correct referenced list and show list from one row above of the actual referenced cell.
=INDIRECT($A$2). It should pick actually from row 3 which is like =INDIRECT($A$3)
Grateful if anyone can help me on it.
Thanks alot
Michel
Hi Michel,
Using $ ties the indirect function to that cell alone
instead Use =INDIRECT(A2) and you can then copy down for the next few rows with no issues.
I can't even pay attention to the content of the article because of the scrolling ad at the bottom of the screen that makes me nauseous.
what if I have like 3 in the first list, 8 in the second list and 5 in the last list
BUT
I have around 705 total projects
will I need to create a 3x8x5x705= 84 600 data in total for it to work?
scrolling toolbar is quite obnoxious. copied/pasted the text I wanted to Word and then went elsewhere. seriously...
I believe there is an easier way to prevent user from changing the value in the first dropdown after making the selection in the second menu (i.e. to prevent mismatches in choices in the primary and secondary dropdowns), easier than the other tutorial linked and definitely easier than VBA:
You can block changes in the first box (or, at least, force the user to clear the second selection before toggling the first dropdown again): changing the formula for the first drop down to =IF(B2="",fruit_list,INDIRECT("fakerange")), as suggested for the simple formula.
For UX, I added a comment to the B2 column header in my actual working sheet (not my data validation sheet) that says "To change the fruit of a line item, first clear the selection in column C by selecting the 'Exporter' chosen, then hit 'delete'". That way, you prevent mismatches, allow the flexibility for changes, empower the user to work autonomously, AND simplify your work considerably. It won't work in EVERY circumstance, but in most, I think this is a much simpler solution.
Hi,
Im trying to create a dependent dropdown where the secondary dropdown elements can come multiple types based on primary selection. Eg: If primary dropdown is Apple, Orange and Grapes and second dropdown is China, Brazil and Australia. If I select Apple - say Australia and Brazil should come and I select Orange it should again populate to China and Australia. Is there any way of doing this as creating a range table for every single dropdown is a tedious task and doesnt work with long dropdowns?
Hello,
I want to have a dependent list based on multiple selections from the first list. How do i make the second list only show values associated with the multiple values in the first?
Example:
1st list is "categories" and you can select multiple answers ("Arts, Business"). 2nd column would provide drop-downs for both "Arts, Business" ("design" (within "arts"), "investing"(within "business").
Another example:
List A
A1 "X"
A2 "X1"
A3
Multi-select A1 and A2
Show values with X and X1 to multi-select.
Thanks a lot! Great tutorial, especially for dynamic drop down!!
i have a problem that is difficult to explain but may well be very easy to solve, my biggest constraint in what i am trying to achieve is that macro is not allowed to be used as the template i am creating is uploaded into a platform that cant accept macros.
Having said that, this is what i want to do: I am using a dependent dropdown list so when i select a particular cell in (column A) with a list, that selection informs another cell adjacent in Column B (another dropdown list) only certain value is available for selection list. I want now to complicate it further because i want to add another dropdown list in column C with a selection of A, B, C etc and hope that when i select A, B or C it will concatenate the data in column B cell. To explain it better, if i select Column A (fruit) Column B list would contain Apple, Pear, Plum, Orange but what if i want to identify the colour of the fruits i would have the colours available in Column C; red, blue, green and pink, so i want to have the option of adding the colours to the fruits in Column B cell.
A question already asked but I didn't find a response ...
Is there a way to automatically update the exporter cell to the first item in the list once the fruit is selected?
hi, is it possible to show 2 named ranges together using data validation. For example list A list of fruits and list B has list of vegetables. in dependent drop down list. we can get if A1 has fruits then List A, and if A1 has Vegetables then List B.
But if A1 = c , i want to the list of both list A and list B, is it possible to do without creating named range including both list.
Thanks Svetlana.. though this article was little bit tricky but i have enjoyed it..
Nice article, but stuck. I have a drop down (master) that needs to change three other cells once you change the drop down.
Ex. Historical list A_L, I_N, X_R, R_T
Hours/tops 174, 47.2, 88.3, 13.2
Metric Type Joy, Rain, Sun, Sad
Source Joy.xls, Rain_GL.xls, Sun_2020.xls, Sad.doc
If Historical List changes A_L to X_R
The other need to go from A_L, 174, Joy, Joy.xls to X_R, 88.3, Sun,Sun_2020.xls
I tried making the first Historical List the primary, and the others customer with a =if(B3=C7:C11,D77:D11,0) where B3 = Cell that changes from any given C7-C11 cells (array) and the D column hours/tops. I didn't continue because that didn't work. I tried various others ways, and I am just frustrated.
Hello Kristy!
I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
Thank you!!!! This was a blessing and helped so much!!!!!
Is it possible to create a dropdown menu that will automatically fill column A, B, C & D? Basically, I need to make one selection, and have the first four columns autofill.
I have tried to follow the instructions above but am getting an error when I put in the formula for the second drop down(The Source currently evaluates to an error. Do you want to continue?) When I chose a selection in the master list, four out of five selections get no response, but one selection (the second option) then fills in the second drop down with options to choose from.
What may I have overlooked or need to modify in order to get the drop downs to work? Your assistance is greatly appreciated.
Thank You
Hello Lin!
I’m sorry but your task is not entirely clear to me.
For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.
I am trying to create a dropdown with options that will then change the option in the second dropdown. I have five options in the first dropdown. I at first used the =INDIRECT($B$24) and then changed to =INDIRECT(B24) and finally have used =INDIRECT(SUBSTITUTE(B24," ","")) as my options in the first drop down have spaces. As I mentioned in the first comment, when I enter the formula for the second dropdown it gives me an error message, The Source currently evaluates to an error. Do you want to continue?, and I am unable to see four out of five of the options in the second dropdown. The second option, which I have as Inventory, will show in the second dropdown but not any of the rest. I am trying to have a person choose one of the options in the first dropdown, which will then give them a second list to choose options from. Such as with my Inventory, then gives the additional options of (Click to Select), Initiate Rental Agreement, Develop Inventory Management Plan, Create a Par Level, Create a sign-out sheet, Other. Right now the first dropdown is in cell B24 and the second dropdown is B35 on Sheet 1 and my list are on Sheet 2.
Hello Lin!
Here is what I could understand. You have the first drop-down list in cell B24. It consists of 5 positions. The second drop-down list is on the same sheet in cell B35. It works with the formula = INDIRECT($B$24).
Next you need to create named ranges. Their names exactly match the values from the first drop-down list. This is described in detail in the manual above. Read it carefully. If you do not, then the second drop-down list will not work.
Perhaps this is the cause of the problem.
No sir, it does not work with any of the formulas. Currently only the second option in my first drop-down will bring anything up in the second drop-down. I currently have the formula =INDIRECT(SUBSTITUTE(B24," ","") as my first drop-down consist of Infection Control, Inventory, Procedure or Protocol, Site or Facility Need, Staffing Shortage. As for the named ranges, yes I have created the named ranges to match the names with the space changed to an _, such as for Infection Control the named range is Infection_Control or for Procedure or Protocol as Procedure_or_Protocol.
Hello Lin!
I do not see your data. Therefore, I can’t name the exact reason. However, remember that the names of your items from the drop-down list must exactly match the names of the named ranges. If the name is "Procedure or Protocol", and the named range is "Procedure_or_Protocol" - this is 100% not working.
Good Evening Alexander. If I'm not mistaken a named range can't have spaces, so how do I make the items in the drop-down match exactly with the named ranges. I was of the understanding that the _ represented a space. How else can I make this work? Would it be better for me to send a copy to you to look at the data or is there another option? I appreciate your time and assistance with this matter.
Hello Lin!
Items in the drop-down list must contain _ instead of a space For instance, Procedure_or_Protocol. Your items from the drop-down list must exactly match the names of the named ranges.
I hope this will help
Hi I would like to create a dependent dropdown list but my data is in this manner and its huge data. I only give 2 deps as example but there is a lot of deps. How can i make a drop down with this where my data in class is dependent on the dep.
Example:
DEPS CLASS
SHOES KIDS
SHOES KIDS
SHOES KIDS
SHOES MEN
SHOES MEN
SHOES WOMEN
SHOES WOMEN
CLOTHING UNISEX
CLOTHING UNISEX
CLOTHING UNISEX
CLOTHING MEN
CLOTHING MEN
CLOTHING WOMEN
CLOTHING WOMEN
Thank you so much if you can help on this.
Hello Mary!
I can’t create a dependent drop-down list for you, because I can’t work with your data. I can only help with advice. But for this, ask a specific question - what is the problem, where the error occurs, which formula does not work.
I want nested dropdown list.
Example:
Fruit >Apple
>Grapes
>Mango
Country >India
>US
In data validation my source are digit ( numbers ) ,hence I get error message as "The source currently evaluates an error ",Do you want to continue ? .How to solve it . If the source is a text then it works fine ,but if it is a number it dosn't work.
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
Thank you! Finally someone with a solution to my problem!