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 a previous dropdown. 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 Exceland 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. Now you need to create names for your main list and for each of the dependent lists.

For the detailed step-by-step instructions please see Creating a named range. 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: 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:. 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 Yesand 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.

If needed, you could add a 3 rd cascading drop-down list that depends either on the selection in the 2 nd drop-down menu or on the selections in the first two dropdowns. 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 3 rd 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:. 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: 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:.

As the result, your 3 rd 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. The INDIRECT formulas like we used in the example above can handle one-word items only.

However, spaces are not allowed in Excel names, which is why this formula won't work with multi-word names. 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 A 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 1 st and 2 nd 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:. 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 2 nd drop-down list, no choices will be available when the user clicks on the arrow next to the first list. 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.

The latter is my preferred way because it provides numerous advantages, the most essential of which are:. 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 to employ Excel tables to store the source data.

Let me remind you that tables were introduced in Excelso you can use this method in all modern versions of Exceland Then switch to the Design tab and 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. 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.

Give it some meaningful and easy to remember name, e. 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 attentively, 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.

Select Drop Down Item and use VLOOKUP to Return Multiple Attributes | Excel University

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. Create a name to retrieve the dependent menu's entries. If you are curious to learn the Index and Match functions in-depth, check out this tutorial: Well, you have already done the major part of the work!

This is actually the easiest part. 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: 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:. Not to make the formula too bulky, create a couple of helper names with the following simple formulas first:.

The screenshot below shows the resulting dynamic drop-down menu in Excel where all blank lines are gone! This is how you create dynamic cascading drop-down lists in Excel. Feel free to download the sample workbooks and see it in action:.

Thanks for the example. My question is how do you prevent someone changing the fruit cell once the exporter cell is chosen? If you want to prevent users from changes the first drop-down once they make a selection in the second, you need to use a special IF formula when creating the main drop-down.

Please see this part for more details: How to prevent changes in the primary drop-down list. I was able to prevent changes in first drop-down after they select a choice in second thanks to the advice. But now, I can enter things off the drop-down menu. Did I inadvertently turn something on? I need help with part of you tutorial.

In creating a dynamic cascading drop down list in excel, under 2. This is a very good question. This name is not assigned to any particular cell or a range of cells. In fact, this is a named formula that retrieves the correct list from your table depending on which selection is made in the first dropdown. The main benefit of using this formula is that you don't have to create an individual name for each entry, one named formula covers them all.

You create this name in the same way as the two previous ones steps 2. I tried this for my project and found I could not fill to the right to copy the data validation across. So for instance in your example I had entries at B1 and B2. I then entered another fruit at C1 different to the fruit entered at B1. I found that B1's dependent drop down list was appearing in cell C2 instead of the dependent drop down list for C1 as I wanted.

I fixed this by removing the absolute reference in Step 2. Instead of preventing changes to a cell once a selection has been made; what if we wanted all the responses to be cleared when a user has made a selection for 1, 2, 3 etc. The point is to reset the responses provided after the new target cell. Is there anyway to achieve this? Right-click the sheet and select view code. Copy and paste the below text. ClearContents End If If Target.

ClearContents End If End Sub. How would you alter the code below if I have more than one lkinked data validations on one worksheet? I'm running into the same issues as Ron. Hi, If I have named my sheets - ie I've called mine Analysis so I just substitute "Sheet1" with "Analysis" or do I need to keep the sheet number?

I am looking drop down list and if I go to list of drop down and select one. I need another drop down from that drop down list. Can it will be possible.

Hi, I have made three dynamic named ranges using offset formula. All these three lists are on different worksheets. I want to make a dependent dynamic dropdown on a 4th sheet. I got this from here!!! How you copy it to the entire column? I tried normal drag select the corner bottom-right and drag below and I destroyed the results in the 2nd column Svetlana - I have got the drop down box process down, and have been able to make them dependent.

Is there a way for me to make this applicable to an entire column, without manually changing the data validation in each cell. Example being, I want column C drop boxes from row 2 to 10, to be dependent on column B drop boxes, ie drop box cell C25 would be dependent on drop box in cell B Right now I can only get everything in column C to tie to B Yes, Using the example I added columns going across so the change for you will be slightly different.

This needs to be changed to reference the cell relative to the current cell The cell where the second list is being used. The "R[-1]C[0]" is the relative reference looking at the value in the cell one row up in the same column. If you have your selections going down in two columns then your relative reference in the INDIRECT will look one cell to the left in the same row or "R[0]C[-1]".

Hi, I followed thoroughly your instructions and they work just great on my desktop Excel. However it doesn't work on the Excel iPad version also not in Excel online. Any ideas it isn't working with the INDIRECT function? Would there be any other ways to reference a cell relatively to the current one?

The point is that the functions are very limited in Excel for iPad and Excel online. Microsoft is constantly trying to improve both versions, but it is still not possible to fulfill some complex tasks there.

Svetlana, I am working on a spreadsheet and I was wondering if it is possible to apply dynamic cascading lists to 3rd or 4th dependent lists. Is this possible, and if so, how would I go about doing it? Is it possible to apply this to multi select drop downs? So, I would select Apricots and Mango using a VBA script and then see the related suppliers to both Apricots and Mango? Is it possible to exclude blank rows which appear through the data list for example in your data table if A2 was blank, currently this would appear as a gap in the drop down list which I wanted to avoid.

Regrettably, there is no quick and simple way to fulfill this task. You'll definitely need to create a temporary table and copy all non-blank rows there. Is there a way to create a single or multiple substitution teachers table with alternative pop-up options in the time-table. Hi, Thank you for a good description, but i have a question, how can i use this multilevel drop-down list not for one cell, do it for more than cells at once. I want to make a list for product, and ask people to fill other fields like that you teach.

I am having same problem as Saleh, how can we fill the multilevel drop-down so it can work for hundreds or thousands rows and not be dependent on what the 1st cell's selection is?

Right now when you fill the row it only brings the drop down list from the 1st selected cell and building this formula row by row can't be the only way to do this repeatedly is it?

In a drop down list how to select a name starting from letter 'T" quickly a particular name from a huge drop down list. Is there a way to automatically update the exporter cell to the first item in the list once the fruit is selected?

Then I copied the validation into subsequent rows. Refer to Karen's comment third from top. I am also facing the same problem. Please advise if any alternate solution. I want to create drop down menus for a consumer to choose from I have them created. Once they select what they want I want to be able to gather and list information in a new sheet that is gathered from multiple different sheets within the same project.

It's kind of a generic example but anything helps. I am trying to create something similar to your example above. But I am using a number drop down of Values Every time I try to create the third dependent drop down, based on the previous two drop downs, it won't create in Name Manager as it says the value I am combining collates to a field in the spreadsheet Win1.

I can do everything I need by putting the values of Zero-Ten in text format but is it possible that the values are based on Zero-Ten but the user selecting shows the Number value If i made a drop down list containing names of fruits and i want a specific number to appear in another cell depending on the selected fruit. How can i make that? Is there a way to copy info in a cell - example: ADM - without having Excel automatically change the "13" to 14, 15, 16, etc.

I've been cutting and pasting and it takes quite a while to accomplish this. I have a small error in implementing it, can you help me in solving it? I created a original list, and a dependent list. However, the dependent list accepts value only if the 1st option of the original list.

If I select any other option other than the 1st, then the dependent list doesnt work. I the drop down list I want to see Tesco Argos Iceland. How to assign same value to a cell using dependent list? Please let me know. I have a column with a heading and values below. I want that when I select on or the other that the values below change. I have made a drop down menu in the one column.

Now I also want to setup such a way that if I choose one entry from drop down menu in column A then I should get value in column B corresponding to my choice in Column A.

Hi, I've been looking on the internet for a while now trying to find what I am looking for. The problem is I don't know what its called. I know hot to do the drop down menu data validation which is easy and fine however, what I want is for when I select an option from my drop down menu, some other cells to be automatically populated with data.

So when I select 4. If so can someone point me in the right direction please. Post 31 that Michael entered is exactly what I am trying to figure out too. Any help would be much appreciated. I would like to create a spreadsheet with one drop down list that then populates 2 other cells. So you select a birkenstock sales toronto name from the drop down list and it automatically fills out the currency type and the commission percentage for that particular company for you.

It will take a while to figure out but just use the logic. IF a cell equals another cell then make the cell with the IF statement return the value if true. I would like to create a 2nd dropdown list based on the first Athens stock exchange market efficiency a filter rule approach of the Column B dependent on the last Char of a result of a 1st dropdown list based on the Column A.

As a Result, if I select "Proc 2" on the 1st dropdown list, I would like to have the following Sub-Procs listed on the 2nd dropdown list: No, you would have to have your main column: Good explanation on dynamic dropdown list and additional points to trace the error when mapping the dependent list to maindropdown list.

I was reffering many sites on this indirect function. None was pointing out to the formula index, prior to the final step indirect function. It was throwing error after possible all tries Cell Reference Error. Well articulated and understandable. I have navigated plenty of google search results but its found to be very simple to understand and implement. Is there a way to use drop down lists to entirely different sets of data? I medical transcription jobs from home no experience india a list of managers and their employees for a "progress tracker"; is there a way I can select one manager from the drop down, their list of employees and all the related data for those employees will be displayed?

Maybe using a drop down is not even the proper fuction for this? But is there a way where after having this list, I will have one more cell where I enter some info. All these details along with dropdown selection and the cell info come together in the final cell separated with semicolon ; Ex: In below ex A1 to A3 are drop downs and A5 is text box where I can manually enter details also A6 is text box where all details are collated and put up and not editable A1: I love your tutorials, very useful and easy to fallow.

I broker online penny stock to enquire if you done a tutorial on how to create dynamic cascading drop-down lists with three columns where column three depends on column one and 2. Thank you so much for your kind feedback. Please check out step 5 "Add a third dependent drop-down list optional " in the first solution in this tutorial.

This INDIRECT doesnt work when Table method is used to create dynamic lists. Please provide a tutorial for when this method is used. I want to allow my user to start typing in the cell where we have applied drop down and then the drop down to show only values matching his entry.

As my list is a big list which makes it difficult to scroll down n select. You can find the links to a couple of scripts that can filter a drop-down list as you type on this page:. I've also read the article "Creating a drop down list in Excel: I would like to know if its possible in excel. If possible, sending me a template would help a lot, or at least please tell me how to.

Thanks a lot in advance! Please check out "5. Add a third dependent drop-down stock market crash september 2001 optional " in this tutorial. It why did the clubpenguin money maker stop the detailed steps on adding the 3rd level.

I am trying to apply this dependent dropdown list to an entire column. What is selected in B2 will affect the dropdown menu in C2. What is selected in B3 will affect the dropdown menu in C3.

How do I apply this rule to the entire column of 20, lines?? Apple - green - red Mango - for juice - for pulp - fresh - tinned. What if your dependent variable on the table is a number. I am how to create drop down options in excel cell this issue. Excel says that vanilla european put option dependent name can't be a number, and I need my 3rd column to be dependent on the 2nd column which is composed by numbers only.

This helped a lot. I created a dynamic drop down list referencing another drop down list, following your steps and it work perfectly.

My problem is now when I try to copy those cells and defined names to a second sheet to have different information shown on sheet2, the defined names still reference the first sheet only. Is there a way to have the entire name ammunition stocks to buy your case the 'fruit' name reference sheet2! Everything is the same as sheet 1, I just need the fruit cell to reference the new sheet instead of the old.

So I have two columns, each being a drop-down list. I want to be able to change either list, based on the choice in the other cell. For example, if i choose "apple" from the drop down list in A1, B1 will then read "red".

But conversely, i would like to choose "pink" from the drop down list in B1, and have it change A1 to "peach". And this applies back and forth so that allied bank pakistan forex rates column, A and B will be updated no matter which list is chosen from, all the while keeping them drop down lists. I have been looking for a way where by if i enter an item's name ie.

Bag, in the next cell the price ie. Is it possible on excel?: I after something similar. My drop down list is either BPay or Bank Deposit. If they select BPay, I want Biller code and Reference to appear in 2 different cells below.

Like wise, if Bank Deposit is selected, I want Acc No, Acc Name, Bank Name and BSB to appear in 4 cells below. Hello, The money markets handbook a practitioners guide download followed your article closely and have managed to achieve what I intended.

However, I am unable to apply it to other cells rows below if i drag the bottom right of the cell where the formulae are working well. What am I doing wrong? Thanks in advance, Shreeram. I have 2 worksheets. One has 14 Columns. Each Column has between 2 and 8 items to select. On the next Sheet I have a drop arrow for one cell to choose one of the 14 column items d3_wisemen trading system to your tutorial However, the next cell needs to give me the items for each column header.

I have tried data validation, list, and since my source is on another sheet: Is this a 2 steps process somewhere, or what am I missing? Hello, I have the 2 dropdowns. The first se;ects a type of machine, once selected the second will display only the material that works on the machine.

When selecting another machine in the first dropdown I would like the second that displays the material to go blank and ready for a selection. Reason is by not going blank the material that is still listed will be associated with the first machine selection making a error. By going blank with a new machine machine is selected in the first dropdown will force the person making the selection to select the correct material thus eleminating the chance of the error. When I select a city from city1 and another city from city2 and from third I select mode as ROAD, then the result should display cost of ticket and time taken.

I've done exactly what you suggested, but it's not updating automatically - I wonder what I'm doing wrong. I'm using Excel I would like to make a drop down from a table where i select an item column A and the description column b automatically appears.

This is an wonderful illustration, thanks! But I'm getting stuck in a problem. My requirement is to create a task and sub-task list and populate that on daily basis.

I mean master sheet will contain tasks like Functional Design, Technical Design etc. There will data validation in user input sheet where user will select a task on daily basis and a sub-task will be populated accordingly. Above example is working only for a particular row.

Because "fruit" refers to cell "Sheet2! What do I need to do to make it applicable for Sheet2! And Each Currency has some special Number Abank USD SPECIAL NO Bbank USD SPECIAL N0 Cbank USD SPECIAL NO same for EUR,CHF,JPY,GBP. Let me give you and example IF Abank selected on DRPList1 and USD selected on DRPList2 show only ABANK USD SPECIAL NO on DRPList3.

IF Bbank selected on DRPList1 and USD selected on DRPList2 show only BBANK USD SPECIAL NO on DRPList3. So can you actually make money on penny stocks can i connect lists if my banks has same currencies and this each s&p 500 volume leaders currencies has different special number.

Thank you soo much for the assistance. I have managed to create a multiple word cascading drop down list. The problem is that the result on the dependent list only references to whatever is on the first row of the main drop down list.

How can i correct this? Please help me to get dropdown list based on the followings: I have a list of names, how to create dropdown list to populate the list of the words to display when the first, then second letter and so on of binary options bonuses without attachments and fraud word typed.

So simply put, if Yes is chosen, let user choose a value in the other cell. Thoughts on how I can get this to work? Great post, very helpful. However, each of the list in my table is mixed, it might look like this; Apricot Apricot Italy Apple Morocco Mango China Chile Apricot. Rather than separate out the individual lists, how can I make the 2nd drop down list only show those values that word match the 1st drop down, i.

Sorry, it's not possible the way you describe. You should add another column and filter it using the formula. I just want to say thank you for your generosity. This tips will make me perform better at work. I made a search for this in the internet for a while, and your website was the only one to post the exact answer. It was a great article it really helped me alot.

Thanks alot for you help. I really appreciate your time and help. I have a spread sheet with 2 dropdown list and it is working fine using indirect function. Problem is when i erase the value in the first dropdownlist column the second dropdown list value stand still.

Is there a way that if I delete the value in the first dropdown the second dropdown will be removed as well? Most likely you need a VBA macro that will react to changes in the how to roll an options trade futures contracts. Sorry, we cannot help you with this task. How can one create dependent value to drop down list?

If in cell B3 one chooses one value from drop down list, excel automatically in cell C3 drops dependent value.

Drop-down List in Excel - EASY Excel Tutorial

Example - in drop down list I choose customer and automatically in next cell I forex obchodnici address for chosen customer. Is that possible to do in excel? I have created my lists. I have the primary drop-down working!

My 2nd drop-down isn't working. I have my ranges named using "offset" formula as the lists may frequently change. When I make the secondary list NOT using offset, it seems to work okay. To help you better, we need a sample table with your data in Excel.

You can email it to support ablebits. Please add the link to this article and your comment number. I have created a drop-down list on a master sheet. I have 22 sheets with identical columns to the master sheet. The only difference between these sheets is how many rows there will be with data entered. Some cells in some rows will be blank- this is necessary. Can you please explain how I can have a drop-down selection link to a specific worksheet and populate the current sheet in this case it would be the master sheet with the data in the cells from the sheet selected in the drop-down?

For example, I would like to select a rep's name in the drop-down box, and it populates with that rep's sheet the rep's sheet will have a 4 digit number as the name of the sheet.

Any and all assistance is greatly appreciated. I have a drop down list A1 with Name, List B1 is phone number, C1 is Text, and D1 is Email. After I click a name I would like the corresponding phone number, Text just the word and Email Just the word in the next drop down. Also be able to choose the same name with a couple of times but with either number, test and or email I can attached a copy of what i have if need be. I have a category for Ex. Business And i Want to show the different type of business by simply clicking on the category of business.

Thank you for posting this tutorial. Using it, I was able to set up the dependent drop down lists I needed. Now I need to figure out how to make them work across multiple tabs.

The problem is stemming from the initial cell reference. It works perfectly on the 1ST FLOOR tab. The problem is, of course, that if you're on 2ND FLOOR, 3RD FLOOR, etc. Is there a way to force the named formula to always reference C5 on the active tab or must I recreate the named formulas for each tab? Hi, I have one master table with an organisation Business units till departments 5 Columns. Now I am looking at the similar Filtering unique values functionality based on my list selection in the other dependent cells.

If I follow this above method, I end up creating around Named ranges which is next to impossible to remember. What is the best way to dynamically get the Unique values depending on the selection of top level hierarchy? The normal filtering in excel on this table shows the unique values, However I want this filtering on the cell that I want the values.

I have to create a document that displays company names in a dropdown list. I understand creating a dynamic cascading dropdown list can allow future edits of the addresses but not sure how to do the same for company name. I've used sumproduct commodities stock market correlation I need to have filters dropdown values of filters on the dashboard, in order to avoid going to sheet2 for applying different filters and instead select a values from dropdown list and it automatically filters the related column in sheet2.

I can share the sheet, anyone plz help me. On my project i need to add a checkbox to the top of a column. If the user selects that checkbox the rest of the data entry cells below become active and visible. If the box remains unchecked then the why did the clubpenguin money maker stop below are shaded out. I don't need to hide them, just color them out in a certain shading so the seem inactive.

Can anyone help me with this please? How to make a third dependent dynamic drop down based on data selected in dow jones stock market widget second dependent drop down? I tried the one mentioned in "5. Add a third dependent drop-down list optional " but after using the Table method for the second drop down, this doesnt work.

I have a searchable dropdown list which is not dependent, but, I need to protect sheet. I have already unlocked cells in Format Cells. My dropdown list won't work after protecting sheets. Thank you for sharing your knowledge. I learn alot from your article, first of all thanks so much. Is it possible through VBA if i choose the drop down 1 the other drop down goes blank for new dynamic selection through name range.

Can i make a list created in one table dependent on selection from the list created in other table. Hi, Im trying to create a Spreadsheet that when i select from a drop down menu in the first coloumn it brings up my selection results in the 2nd coloumn. I want to create a drop down menu that selects a code however within the drop down menu I want to include what the code's description is. However I only need the cell to be populated with the code not the description.

I have 3 tables with data, that contain some scattered blank rows. I have created data validation lists that can hide these blank rows, until they are populated later on.

My question is this:. Based on the above, can I use some form of an INDIRECT in the data validation process, so that the "correct" list will show up, based on what the user has selected from another column?

In the past, I could use the INDIRECT function, if I named the range in the formulas. Because of the formula I am using to hide blank data, I cannot figure out how to attach this to an INDIRECT.

Thanks for any help! Hi Svetlana I thank you for your post, I found a different way to get to a solution to the cascading drop down boxes. My situation has four levels to select and I didn't want to make named ranges for all of the possibilities. If I'd found your post first I would have tried the option using the tables. My solution involves having a pair of columns of data for each level after the first. I used a pivot to generate each set of data as by base data is subject to change.

I then use a match and countif combination to build an address range based on the previous selection. My method how to make fast money in runescape non member dealing with a change at a quickest way to get money saints row 3 selection level was to clear the selections to the right of the change using an on-update event.

It's all a bit convoluted but it works. I'm happy to send it to you if you'd like to have a look. My description may be a bit brief to convey how it works. Thank you very much for sharing your solution.

I think I got the general idea though not the details. The table option would probably be easier to implement but your approach is original and unique, at least I've not encountered it anywhere else. I have a main validation list consisting of 3 values A, B or C. The second validation list has values of A1 to A5, B1 to B5 and C1 to C5. Is it possible to set the second list to only show values dependent on the choice made from the first. HI, Your tutorial was excellent for what I needed.

Is there a way to add a search field to each of the Columns. Some of the lists are long and rather then scrolling, it would be helpful to add a search or filter field when they first click on the drop down menu for either of the first 2 columns.

Thank you Svetlana, I found this very helpful. I was trying it with another posting, but he didn't mention the use of a single word for the titles.

Once I changed it - Excel was happy. One additional function i need to know. First column will be DropDown List and second column just shows the specific date. I have an issue regarding cascading excel dropdown lists that I'm looking forward to solve it as soon as possible I created a cascading dropdown list with several dependent entries.

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. 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.

I need a formula or another method rather than indirect function. 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 Set A of drop down relates to cats, Set B relates to dogs. 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. I have a long drop down list. Is there a way where typing the first letter will start filtering the drop down list i. Hello, I apply the same way and it works for only different values. 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? 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. Any help is appreciated! Can anyone help please. I need to create 2 pick lists in excel for translation service as follows: 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:.

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. Hi have a hazard library with 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.

Hi, Is there a way to reference a single primary drop down list 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. Hi, this post is really awesome. I have 2 questions: 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. 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!!! Please, look at these example pics ; 2. For more details go here.

How to Add a Drop-Down List to a Cell in Excel

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. 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, 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.

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. This comprehensive set of time-saving tools will help you accomplish any task impeccably without errors or delays. These 8 tools will boost your inbox productivity and simplify your emailing routine.

Making a cascading dependent Excel drop down list by Svetlana Cheusheva ExcelExcel tips Comments. Creating a simple Excel dependent dropdown list Cascading drop-down lists with multi-word entries Block changes in the primary drop-down list Make a dynamic depending drop-down list How to create a simple cascading dropdown in Excel Making simple dependent drop-down lists in Excel is easy. Type the entries for the drop-down lists.

Please pay attention that if your first row is sort of column header like you see in the screenshot above, you shall not include it in the named range. The displayed list of regions is unique for each country but it does not depend on the selection in the first drop-down list.

Use appropriate cell references to copy drop-down lists across the worksheet. When working with dynamic cascading drop down lists created with the above formulas, nothing prevents the user from changing the value in the first dropdown after making the selection in the second menu, as a result, the choices in the primary and secondary dropdowns may mismatch.

You can block changes in the first box after a selection is made in the second one by using either VBA or complex formulas suggested in this tutorial. October 30, at 8: Hi Thanks for the example. October 31, at 8: Hi Terry, If you want to prevent users from changes the first drop-down once they make a selection in the second, you need to use a special IF formula when creating the main drop-down. August 10, at 3: Where do i place this name.

October 31, at 9: Hello Ferrol, This is a very good question. November 10, at 1: I'm not sure if any of this made sense. I hope it has. I have to thank you very, very much for this instruction. It has been a great help. October 27, at 6: July 17, at 8: November 10, at 9: HI Karen How can these drop downs work in another sheets of same workbook. November 12, at 4: Hi, Instead of preventing changes to a cell once a selection has been made; what if we wanted all the responses to be cleared when a user has made a selection for 1, 2, 3 etc.

Thanks a lot in advance. April 14, at 1: November 19, at 5: For example I have the same situation as below but in columns D, E, F, etc.? July 26, at 3: September 14, at 9: Also how do I actually get this to work - do I need to run it? April 6, at 1: November 12, at 8: November 14, at 4: Thank YOu very very very Much November 19, at November 20, at 5: December 29, at 8: May 14, at 7: May 15, at 3: November 26, at 2: April 2, at 8: November 30, at 9: December 4, at Hello Matt, Regrettably, there is no quick and simple way to fulfill this task.

January 12, at December 1, at 5: Hi, Is there a way to create a single or multiple substitution teachers table with alternative pop-up options in the time-table. Hello Sangita, Please specify what you mean by "alternative pop-up options".

December 8, at December 14, at 1: December 20, at 2: March 18, at 1: December 20, at 6: December 30, at 9: January 5, at 8: January 8, at January 21, at 7: January 9, at 4: January 12, at 9: January 22, at 7: January 27, at 3: Hi Svetlana, I am trying to create something similar to your example above.

I can do everything I need by putting the values of Zero-Ten in text format but is it possible that the values are based on Zero-Ten but the user selecting shows the Number value Thank you Mike. January 28, at 4: January 30, at 5: Thanks for any help you can provide. February 3, at Thanks for this extremely helpful article. Can you let me know how to rectify it?

February 13, at 4: Hi, could you tell me how to make cascading dependent Excel drop down list where entries repeat, but I only want it be mentioned once in the drop down list? February 24, at 8: March 3, at 5: March 3, at Hello, I have made a drop down menu in the one column. March 11, at March 18, at 4: ColumnA ColumnB ColumnC Company name Currency Commission So you select a company name from the drop down list and it automatically fills out the currency type and the commission percentage for that particular company for you.

Many thanks in advance, James. March 31, at 2: March 24, at 9: Is it possible to create a dependent dropdown list based on two columns? Col A "Proc 1" "Proc 2" "Proc 3" "Proc 4" Col B "1. September 20, at 5: April 1, at 5: Hi my question is how to create main dropdown option ex i created dorp down option where ever i check a jack b tom c harry in cells if i want to know total numbers of onyone like how many tom are there then what to do?

April 10, at 9: Many Thanks for this post: April 17, at April 20, at April 23, at 3: HI, Very good article. Apple is good for health A6: Apple is good for health. April 23, at 5: April 27, at 3: Hello, I love your tutorials, very useful and easy to fallow. Hello Judita, Thank you so much for your kind feedback. March 15, at 2: Hi Svetlana, This INDIRECT doesnt work when Table method is used to create dynamic lists. April 27, at 7: Hello, I am trying to create a pricing list using 2 text drop-down lists.

April 30, at 8: April 30, at 9: Hi Aghil, You can find the links to a couple of scripts that can filter a drop-down list as you type on this page: May 5, at Hello JIrman, Please check out "5. May 22, at 6: When I select the whole column to apply this rule to, C3's menu depends on B2 instead of B3.

June 11, at 1: I'm having the same issue, did you ever figure out how to remedy this? May 27, at 3: Is it possible to create sub-choices within a drop down list? June 1, at 3: Hello, What if your dependent variable on the table is a number. June 2, at 9: Hi This helped a lot. November 10, at June 5, at 2: Svetlana, So I have two columns, each being a drop-down list.

Let me know if you can help! That'd be amazing -Sam. June 9, at 7: June 11, at June 25, at 2: July 2, at 9: Great Tutorial Thank you! July 16, at 1: July 27, at 9: July 28, at 8: Hi all, How can add a Validation Button On My Sheet. How can add the sheet Which is in Drop Down List. July 31, at 8: August 8, at 3: August 26, at 2: September 1, at 3: September 3, at 1: September 21, at 2: September 29, at 6: I have one question.

I have Banks Abank Bbank Cbank And Eachbank has same currency USD GBP EUR CHF JPY And Each Currency has some special Number Abank USD SPECIAL NO Bbank USD SPECIAL N0 Cbank USD SPECIAL NO same for EUR,CHF,JPY,GBP I this stiuation When i selected First Dropdown i wanted to show Banks- Second dropdown wanted to show All currencies- Third dropdown wanted to show Special no Let me give you and example IF Abank selected on DRPList1 and USD selected on DRPList2 show only ABANK USD SPECIAL NO on DRPList3 IF Bbank selected on DRPList1 and USD selected on DRPList2 show only BBANK USD SPECIAL NO on DRPList3 But it is always showing ABANK USD SPECIAL NO DRPList3 So how can i connect lists if my banks has same currencies and this each same currencies has different special number Thanks.

October 6, at 2: Good day, Thank you soo much for the assistance. October 12, at 2: Sir, Please help me to get dropdown list based on the followings: November 11, at November 26, at 5: I have 2 columns in a spreadsheet, both are picked from a drop down list. November 30, at Hi Great post, very helpful. However, each of the list in my table is mixed, it might look like this; Apricot Apricot Italy Apple Morocco Mango China Chile Apricot Rather than separate out the individual lists, how can I make the 2nd drop down list only show those values that word match the 1st drop down, i.

Probably not the most elegantly written post. January 28, at Hello, Paul, Sorry, it's not possible the way you describe. December 1, at 4: December 2, at December 2, at 5: Hi, I have a spread sheet with 2 dropdown list and it is working fine using indirect function.

Hello, Mike, Most likely you need a VBA macro that will react to changes in the cell. December 3, at 6: Hello, Zane, Looks like you need VBA. Sorry, we cannot help you with this. December 19, at 8: January 13, at 3: January 21, at 4: Hello, Trey, To help you better, we need a sample table with your data in Excel.

January 28, at 7: January 31, at February 3, at 1: I want to make a list using the following: Business And i Want to show the different type of business by simply clicking on the category of business can you help me how to make this in excel. February 11, at 1: February 14, at 6: I want to run a macro on the basis of different selections from drop down list, is this possibl? February 18, at 3: February 26, at 7: March 5, at 7: March 11, at 5: Hi, Love this tutorial.

Thanks for the tutorial. March 17, at 7: March 20, at March 20, at 2: Sir, I learn alot from your article, first of all thanks so much. Please send me solution. March 24, at March 30, at 1: Hello All, Is it possible through VBA if i choose the drop down 1 the other drop down goes blank for new dynamic selection through name range. April 3, at 6: April 18, at 8: April 23, at 7: May 16, at 8: May 24, at 9: Hi I want to create a drop down menu that selects a code however within the drop down menu I want to include what the code's description is.

Supplier Code Mountain Warehouse Select only for the cell. July 12, at 4: Hello, I have 3 tables with data, that contain some scattered blank rows. My question is this: July 19, at July 20, at 9: Hi Ron, Thank you very much for sharing your solution. July 20, at 3: August 4, at 5: August 11, at 6: Hi, I want to know if it is possible to put different lists in the same dropdown.

August 17, at 8: September 6, at 5: Thanks for the great tutorial. September 6, at This tutorial helped me a lot It is the following, I created a cascading dropdown list with several dependent entries.

September 14, at 7: September 30, at Hello, I have a question, I have a dropbox with 2 options. November 8, at 1: Dependent drop down is not working for multiple sheets. November 22, at 7: December 6, at 7: December 14, at December 16, at 6: December 21, at 6: January 3, at