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 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.
Type the entries you want to appear in the main and dependent drop-down lists.

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.
Creating a named range

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

For the detailed step-by-step instructions please see Creating a named range.

Things to remember:

  1. 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.
  2. 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.
Verifying the ranges' names and references

3. Make the first (main) drop-down list.

  1. In the same or in another spreadsheet, select a cell or several cells in which you want your primary drop-down list to appear.
  2. 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.
    Select the cell(s) in which you want your primary drop-down list to appear and apply Excel Data Validation.

    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:
    The primary drop-down list in Excel

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.
Set up the dependent 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.
The dependent drop-down list in Excel

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)
Creating a 3rd dropdown that depends on the selection in the 2nd list

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:
A 3rd dropdown depending on the selection in the 2nd drop-down menu.

Note. The displayed list of regions is unique for each country but it does not depend on the selection in the first drop-down list.
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:

  1. 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.
    Create additional sets of named ranges.
  2. 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.
    A third dropdown dependent on the selections in the first two 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:

  1. 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.
  2. 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.
  3. 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," ",""))
Creating a cascading drop-down list with multi-word entries

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.
Preventing changes in the primary drop down list

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:

  1. You have to create 3 named ranges only, no matter how many items there are in the main and dependent lists.
  2. Your lists may contain multi-word items and any special chars.
  3. The number of entries can vary in each column.
  4. The entries' sort order does not matter.
  5. Finally, it's very easy to maintain and modify the source lists.

Okay, enough theory, let's get into 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 to employ Excel tables to store the source data.

Let me remind you that tables were introduced in Excel 2007, so you can use this method in modern versions of Excel 2016, 2013, 2010 and 2007.

Once you have entered the data, select all of the entries and click Insert tab > Table. 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. 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.
Organize the source data in a table.

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.
Create a name for the table's header row.

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:

Create a name for the cell containing the primary drop-down list.

Tip. Use appropriate cell references to copy drop-down lists across the worksheet.

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

Coping dependent drop-down lists to other columns.

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.

Coping dependent drop-down lists to other columns

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.
Create a name to use for the dependent dynamic menu.

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:
Open the Name Manager 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).

Setting up Excel Data Validation for the dynamic cascading drop-down list

Done! Your dynamic cascading drop-down menu is accomplished and will update automatically reflecting the changes you've made to the source table.
Dynamic cascading drop down menu in Excel

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:
Blank rows appear in the dependent drop-down menu.

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!
 The dynamic cascading drop-down menu in Excel without any blank lines

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

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:

202 Responses to "Making a cascading (dependent) Excel drop down list"

  1. Terry says:

    Hi

    Thanks for the example. My question is how do you prevent someone changing the fruit cell once the exporter cell is chosen?

  2. ferrol says:

    I need help with part of you tutorial.
    In creating a dynamic cascading drop down list in excel,
    under 2.3. Create a name to retrieve the dependent menu's entries.

    Which cell or range of cells gets =INDEX(exporters_tbl,,MATCH(fruit,fruit_list,0)) as its name.

    Where do i place this name

    • Hello Ferrol,

      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.1 and 2.2) by clicking Formulas > Name Manager > New, or by pressing Ctrl + F3 and then clicking New.

      • lilac says:

        Dear Svetlana,
        I tried Step 2.3 - and it keeps throwing me error.
        In Name Manager - the Refers To section keeps on putting quote around INDEX ie. ="Index(...)"
        If I delete the quote, it says There's a problem with this formula and I cannot save it.
        Hence the Value in Name Manager doesn't evaluate.
        I copied your example to the tee and I'm using Excel 2016
        Is there a solution for this?
        Many thanks.

  3. Karen says:

    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.2 as follows
    = Sheet2!B$1 //ie no $ before the B
    I was then able to fill the data validation from B2 to the right, so that C2 was dependent on C1; D2 was dependent on D1 etc.

    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.

    • Ana says:

      Awesome! that was my issue, thanks!

    • Shosh says:

      Thank you! That was my problem too!

    • Anik Sachdeva says:

      HI Karen
      How can these drop downs work in another sheets (of same workbook)

    • Kleber Bonitese says:

      Nice! I also had this issue, since I am doing this in a form... So for "fruit" I named an interval like "=Sheet2!$B5"

      This way all the cells in the columns are now working well. Hey, Svetlana, maybe it would be worth it to add this to your tutorial, since many people seem to need it variable and not just for a single cell.

      Cheers guys!

  4. Emma says:

    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. and then decides to go back to 1 to change the response.

    For instance, I've got three drop down lists in C1, C2 & C3 and then want C2 & C3 reset to blank, when I change C1; or maybe C3 to blank, when I change C2. The point is to reset the responses provided after the new target cell. Is there anyway to achieve this?

    Thanks a lot in advance.

    • Tommy says:

      Try using macros: Right-click the sheet and select view code. Copy and paste the below text. Enable macros.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = "$C$1" Then
      Worksheets("Sheet1").Range("C2,C3").ClearContents
      End If
      If Target.Address = "$C$2" Then
      Worksheets("Sheet1").Range("C3").ClearContents
      End If
      End Sub

      • Ron says:

        How would you alter the code below if I have more than one lkinked data validations on one worksheet?

        For example I have the same situation as below but in columns D, E, F, etc.?

        Thanks

        Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$1" Then
        Worksheets("Sheet1").Range("C2,C3").ClearContents
        End If
        If Target.Address = "$C$2" Then
        Worksheets("Sheet1").Range("C3").ClearContents
        End If
        End Sub

        • Samatha says:

          I'm running into the same issues as Ron. I have 1000+ rows that have multiple dependent drop-down lists; how do I edit the code so that it applies to all rows?

      • Marcia says:

        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?

        Also how do I actually get this to work - do I need to run it? or save it as a macro name?

        Many thanks
        Marcia

    • drop down to sub drop down says:

      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

  5. Rahul says:

    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. is it possible ? and if yes, how?

    for ex: store1, store2, store3 these are three named ranges on sheet a , b, c respectively. now i want to make a dependent dropdown in which first dropdown should have store1, store2 , store 3 and the second dropdown must have the items listed in these.

  6. Umair says:

    I was Struggling for dependent list -- how to create in excel /// After 1 and half year ... I got this from here .............!!!
    O My Good.

    Thank YOu very very very Much ......... :)

  7. Ciprian T. says:

    Hello guys,
    question related to - dynamic cascading drop-down lists
    I followed the steps, done = working for the first cell (B1 in your example)

    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 ....same thing happened when I did it in your sample
    Since I need hundreds of rows daily, doing B1 procedure in all manually is not possible
    Thanks for the great help you provide so far

  8. Jason says:

    Svetlana - I have got the drop down box process down, and have been able to make them dependent. My question is: 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,000) to be dependent on column B drop boxes, ie drop box cell C25 would be dependent on drop box in cell B25. Right now I can only get everything in column C to tie to B2...

    Thanks!

    • Steve says:

      Yes,
      Using the example I added columns going across so the change for you will be slightly different.
      The key is the Name "col_num" in the example. It uses the =MATCH(fruit,fruit_list,0) which references the named cell "fruit". This needs to be changed to reference the cell relative to the current cell (The cell where the second list is being used.
      I changed this to use the INDIRECT function to reference the cell one row up so my full function used in the "col_num" name is: =MATCH(INDIRECT("R[-1]C[0]",FALSE),fruit_list,0)

      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]"

      • Raimonds says:

        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). Whereas the static/fixed reference (ie "=MATCH(fruit,fruit_list,0)") works both on desktop as well as on iPad. 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?
        Thanks!

        • Alexander says:

          Hi Raimonds,

          Unfortunately, we can’t say for sure why the solution doesn’t work on the Excel iPad (in Excel online) and can’t suggest an alternative. 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.

  9. Gregory says:

    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?

  10. Christina says:

    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?

    Thanks a lot!

  11. Matt Cook says:

    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.

    Thanks

  12. Sangita says:

    Hi,

    Is there a way to create a single or multiple substitution teachers table with
    alternative pop-up options in the time-table.

  13. Naeem says:

    Hi,

    Instead of single value / item, i wish to display some bullet points for 2nd dependent drop down against selection in the first dropdown.

    Please reply ASAP.

  14. jam says:

    how can i return a certain data using VLOOKUP??? for example using just an id number i have to return the name of that id number user? is that possible in data validation???

  15. Saleh says:

    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 100 cells at once.
    I want to make a list for 100 product, and ask people to fill other fields like that you teach. but I cant do it for 100 products one by one. Help me !!

    • AC says:

      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? HELP!!

  16. Sajan says:

    In a drop down list how to select a name starting from letter 'T" quickly a particular name from a huge drop down list.

  17. Alex says:

    Is there a way to automatically update the exporter cell to the first item in the list once the fruit is selected?

  18. Eric says:

    Using your instructions above, I’ve been able to create my dynamic, cascading validation successfully. Then I copied the validation into subsequent rows. My problem is that when I’ve selected from the dropdowns in the first row, the dependent dropdowns for subsequent rows are stuck matching the first row. Can you tell me what I’m doing wrong? Thanks!

  19. Fawwad says:

    @Eric remove the "$" from the dynamic location of the cell. Refer to Karen's comment (third from top). She faced same issue for columns and removed $ from the column letter. For rows, keep the $ for row letter but remove it from row number

    So your reference to the first cell would become from
    = Sheet2!$B$1

    to
    = Sheet2!$B1

    I have tried this and it works a treat!!

    • Kra says:

      I am also facing the same problem.
      @Fawwad removing the "$"from the dynamic location of the cell is not working. Please advise if any alternate solution. Thanks !

  20. Alex M says:

    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. I want to basically write an if/then statement that if they select this from the drop down list and something from a different drop down list then a master list appears with what they need. It's kind of a generic example but anything helps.

  21. Andy says:

    Thank you.

  22. Hamza says:

    your work is helping me a lot. i would like to know that if i select a certain option from drop down and fill some data accordingly then if i change the drop down selection then the data should change accordingly.how do i do it?

  23. Mike says:

    Hi Svetlana,

    I am trying to create something similar to your example above. But I am using a number drop down of Values 0-10. 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 0-10

    Thank you

    Mike

  24. Hady says:

    I want know. 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 ?
    Thanks in advance.

  25. Sherrie says:

    Is there a way to copy info in a cell - example: ADM-90-13 - without having Excel automatically change the "13" to 14, 15, 16, etc. as it populates the next cell. I've been cutting and pasting and it takes quite a while to accomplish this.

    Thanks for any help you can provide.

  26. Kaddy says:

    Hey Hi!

    Thanks for this extremely helpful article.

    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.

    Can you let me know how to rectify it?

  27. Judita says:

    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? E.g. first row - Supplier Tesco Tesco Tesco Argos Iceland Iceland. I the drop down list I want to see Tesco Argos Iceland.

    Thanks!

  28. Rahul says:

    How to assign same value to a cell using dependent list?
    For(your)example: how can I assign/populate same exporters for two fruits say 'Mango' and 'Apricot' dynamically without duplicating the values?
    Please let me know.

  29. Kironde says:

    I have a column with a heading and values below. the heading has a drop down with two selections. I want that when I select on or the other that the values below change. For eg. if i have a different figure for budget and target I would have budget and target in the drop down and the figures below would change depending on which one I selected.

  30. Meet says:

    Hello,

    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.

    Thanks

  31. Michael says:

    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.

    I.e.

    Pressure Torque
    4.0 BarG 20Nm
    5.0 BarG 40Nm

    So when I select 4.0BarG from the drop down menu, I want another cell to be automatically populated with 20Nm. Is this possible? If so can someone point me in the right direction please.

    Hopefully someone can help/advise me what this function is called so I can search better.

    Thanks!

    • James says:

      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.

      I.e.

      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.

      Is that possible?

      Many thanks in advance,

      James

    • Anonymous says:

      Use IF statements. 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.

  32. LCoelho says:

    Is it possible to create a dependent dropdown list based on two columns?
    For example:

    Col A
    "Proc 1"
    "Proc 2"
    "Proc 3"
    "Proc 4"

    Col B
    "1.1 Sub-Proc"
    "1.2 Sub-Proc"
    "1.3 Sub-Proc"
    "1.4 Sub-Proc"
    "1.5 Sub-Proc"
    "2.1 Sub-Proc"
    "2.2 Sub-Proc"
    "2.3 Sub-Proc"
    "3.1 Sub-Proc"
    "3.2 Sub-Proc"
    "3.3 Sub-Proc"
    "3.4 Sub-Proc"
    "4.1 Sub-Proc"
    "4.2 Sub-Proc"

    I would like to create a 2nd dropdown list based on the first Char 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:
    "2.1 Sub-Proc"
    "2.2 Sub-Proc"
    "2.3 Sub-Proc"

    Tks in advance
    LCoelho

    • Richard says:

      No, you would have to have your main column:
      Procedure 1
      p...2
      p...3
      then your headers would have to be
      p...1, p...2, p...3 etc..

      and your sub proc. would have to be under their respective col.

  33. saklain says:

    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 500 cells if i want to know total numbers of onyone like how many tom are there then what to do ?

    Need your help

  34. Asha says:

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

    Many Thanks for this post:)

  35. Amit Bhardwaj says:

    You rocks!!

    Well articulated and understandable. I have navigated plenty of google search results but its found to be very simple to understand and implement.

    Thanks much

  36. David says:

    Is there a way to use drop down lists to entirely different sets of data? E.g. I have 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?

    Thank you!

  37. Varsha says:

    HI,

    Very good article. Thanks 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 : Health
    A2 : Fruits
    A3 : Apple

    A5 : Apple is good for health

    A6 : A1:Health ; A2:Fruits ; A3:Apple ; A5: Apple is good for health

  38. Judita says:

    Hello,

    I love your tutorials, very useful and easy to fallow. I wanted 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

    • Hello Judita,

      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.

      • Swathi says:

        Hi Svetlana,

        This INDIRECT doesnt work when Table method is used to create dynamic lists. Please provide a tutorial for when this method is used.

  39. Lee says:

    Hello,

    I am trying to create a pricing list using 2 text drop-down lists.

    Column A "Install" + Column B "Red Cabinet" = $2,500.00 (from a pre-made list)
    Column A "Move" + Column B "Blue Cabinet" = $2,000.00 (from a pre-made list)
    Column A "Remove" + Column B "Green Cabinet" = $500.00 (from a pre-made list)

    Any help you could provide would be greatly appreciated.

    Thanks!

    Lee

  40. aghil says:

    hi,

    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.

  41. Anonymous says:

    Hi, great article!
    I've also read the article "Creating a drop down list in Excel: static, dynamic, from another workbook" and I learned a lot!
    Right now I'm trying to make a 3 level cascading list, Country, Province/State, City/Municipality, that is editable in the future.
    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!

  42. JIrman says:

    Hi, great article!
    I've also read the article "Creating a drop down list in Excel: static, dynamic, from another workbook" and I learned a lot!
    Right now I'm trying to make a 3 level cascading list, Country, Province/State, City/Municipality, that is editable in the future.
    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!

  43. Nicole says:

    Great tutorial! I am trying to apply this dependent dropdown list to an entire column. Eg. 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,000 lines)??

    When I select the whole column to apply this rule to, C3's menu depends on B2 instead of B3.

    Please help!

  44. Chris says:

    Is it possible to create sub-choices within a drop down list?

    Apple
    - green
    - red
    Mango
    - for juice
    - for pulp
    - fresh
    - tinned

    Similar to the menu > sub-menu > sub-sub menu > sub-sub-sub menu cascade that you have with Windows "All Programs"

  45. fernando says:

    Hello,

    What if your dependent variable on the table is a number.
    I am having this issue. Excel says that the 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).

    Thanks

  46. Nathan says:

    Hi

    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 (in your case the 'fruit' name) reference sheet2!A5, sheet3!A5, etc? Everything is the same as sheet 1, I just need the fruit cell to reference the new sheet instead of the old.

  47. Sam says:

    Svetlana,

    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 each column, A and B will be updated no matter which list is chosen from, all the while keeping them drop down lists.

    Let me know if you can help! That'd be amazing

    -Sam

  48. Sarah says:

    Hi there!

    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. $19.50 would automatically appear. Is it possible on excel?:)

    Thank you!
    -Sarah

  49. Dan says:

    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.

    Is this possible?

    Thank you
    Dan

  50. Shreeram says:

    Hello,
    I followed your article closely and have managed to achieve what I intended. Thanks a ton.
    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

  51. Erin says:

    Great Tutorial Thank you!

    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 (thanks 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: =Indirect(SUBSTITUTE(Sheet2!$A$1:$D$7))I then get an error message. Is this a 2 steps process somewhere, or what am I missing?

    Thank you,

    Erin

  52. Jim says:

    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.

  53. Utsav says:

    I want to select data from three drop down list ddl1, ddl2 & ddl3 and the result should be shown by matching the details.

    For example: I have ddl city1 & city2 and third ddl as mode_of_transport.

    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.

    Is it possible ??

  54. Sumit says:

    Hi all,

    How can add a Validation Button On My Sheet. How can add the sheet Which is in Drop Down List

  55. Fabiana says:

    It's really weird. I've done exactly what you suggested, but it's not updating automatically - I wonder what I'm doing wrong. I'm using Excel 2013.

  56. jim quest says:

    Not sure if I understood it

  57. pratik says:

    article is very good but i have one query..

    in C column i want to show some data dependent on what select in B column. i know by using indirect function we can do that. but how to implement this logic to entire column? can you please help becuase it is very hard to select each cell update indirect(b2), indirect(b3)...etc...

  58. LAURA says:

    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. Is this possible?

  59. Ritam Sinha says:

    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.; sub-tasks like preparation, review, rework 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!$B$1". What do I need to do to make it applicable for Sheet2!$B$2..$B$10 and so on?

  60. Delibas says:

    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 111
    Bbank USD SPECIAL N0 112
    Cbank USD SPECIAL NO 113
    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 111 on DRPList3

    IF Bbank selected on DRPList1
    and USD selected on DRPList2
    show only BBANK USD SPECIAL NO 111 on DRPList3

    But it is always showing ABANK USD SPECIAL NO 111 DRPList3

    So how can i connect lists if my banks has same currencies and this each same currencies has different special number

    Thanks

  61. Nteng says:

    Good day,

    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?

  62. Pandharinath Kerkar says:

    Sir,

    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 the word typed.

  63. Ricardo says:

    Thanks for the tutorial.
    Help me a lot with my problem.

  64. Santosh Budakoti says:

    I have 2 columns in a spreadsheet, both are picked from a drop down list.

    Col A is "Do you want more information" - Possible Values Yes\No

    Col B is "How did you hear about us" - Possible values are Email\Phone\Other

    Question: If "No" is chosen in Col A, then Col B's value should be N/A, otherwise the user should be able to choose from the dropdown and pick a value. Using the following formula, I can get N/A to appear, but the "false" option overwrites any available dropdown values or previously selected values. So simply put, if Yes is chosen, let user choose a value in the other cell. If No is chosen, show N/A in the other cell. Thoughts on how I can get this to work? or how it's work with data validation?

  65. Paul says:

    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.e Apricot Italy and Chile Apricot?

    Probably not the most elegantly written post.

    Cheers

  66. Iván Calderón says:

    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.

  67. Nitin Goswami says:

    It was a great article it really helped me alot.Thanks alot for you help. I really appreciate your time and help. Thanks again :-) buddy

  68. Mike Bautista says:

    Hi,

    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?

    Thanks.

    Mike

  69. Zane says:

    hello!

    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.
    Example - in drop down list I choose customer and automatically in next cell I get address for chosen customer. Is that possible to do in excel?

  70. SURIBABU says:

    i have small problem in excel. it is i am created a drop down in the excel. like this Ex: B is 1,2,3,4,5.... and C is 45,56,59,58,26..
    but iam select B1 then automatically C Cell become changed into 45.
    how is it please solve my problem. thanking you

  71. Guillermo Jimenez says:

    Very helpful. Thanks!

  72. Trey says:

    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.

    Any tips?

  73. Liz says:

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

  74. Woody says:

    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

  75. Gerald says:

    I want to make a list using the following:

    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

    can you help me how to make this in excel.

    Thank you.

  76. Jason says:

    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. Here's my example:

    NAME: CELL_1_DOOR_TYPE
    FORMULA: ='1ST FLOOR'!$C$5

    It works perfectly on the 1ST FLOOR tab. The problem is, of course, that if you're on 2ND FLOOR, 3RD FLOOR, etc., it references C5 on the 1ST FLOOR tab.

    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?

    Thanks in advance!

  77. Ahtisham says:

    I want to run a macro on the basis of different selections from drop down list, is this possibl?

  78. Vijay says:

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

    Pl. help
    Vijay

  79. Eric says:

    I have to create a document that displays company names in a dropdown list. Once selected. i want another cell to display a dependent dropdown list to show addresses for the selected company name. The only issue is i want to be able to add/remove company names AS WELL as the addresses listed under each.

    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 want to create a file for someone with no excel experience to be able to add/remove both company name and its associated addresses.

  80. haffy says:

    I've used sumproduct(subtotal(....)) formula on dashboard to dynamically calculate values form a table, by applying filters to the table on sheet2. 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.

  81. Gareth says:

    Hi,

    Love this tutorial. 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 cells 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?

    Gareth

  82. Swathi says:

    Hi. Thanks for the tutorial.

    How to make a third dependent dynamic drop down based on data selected in the second dependent drop down?

    • Swathi says:

      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

  83. Carmen says:

    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. I also have some basic VBA coding to allow grouping/ungrouping, and formatting columns after protecting cells.

    My formula for data validation is =BillTo

    My formula for the name 'BillTo' in Name Manager is using =OFFSET(Data!$D$2,,,COUNTIF(Data!$D:$D,"?*"))

    Can you please help me??

    Thank you!

  84. Ken says:

    Thank you for sharing your knowledge. This combined with t&e vlookup command will fo just what i need.

  85. sanjay says:

    Sir,

    I learn alot from your article, first of all thanks so much.
    but i face a problem after creating dependent Drop down, when i select a value from a drop down and almost complete my app entries and then i change in dropdown list it will not updated in the my previous selected selectioon

    like example list

    Fruit
    Oranges
    Apple

    Now i update Apple to Kashmiri Apple, it must be updated everywhere where i selected Apple.

    Please send me solution

  86. beginner says:

    how do you put multiple entry in one column for data validation?

  87. Pankaj says:

    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.
    please post for any further clarification on the same.

    Reagrds
    Pankaj

  88. Atisham says:

    Can i make a list (created in one table) dependent on selection from the list (created in other table)

  89. Grant says:

    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.

  90. Nair Enolam says:

    Thanks to this tutorial I have my sheets working exactly as I need them but now I want to go a step further and use the selected item from the drop down to create a product/model number.

    eg A supplier in Pakistan(PA) of Mangoes(MA) was my first ever supplier(001), they also supply Yams(YA)

    so in that example there would be two product numbers PAMA001 & PAYA001

    I have used the same process above to create corresponding tables with the data used to build the product/model number

    where do I look to find a way to do that, if it's actually possible

    cheers Nair

  91. avi says:

    hi svetlana,
    how can I make the list to have multiple selection, I need more than one selection - let say for example mango, orange and apple ?

  92. Bernard says:

    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. However I only need the cell to be populated with the code not the description.

    Supplier Code
    1234 Mountain Warehouse

    Select only 1234 for the cell.

    Is this possible?

  93. Brendan says:

    Hello,

    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!

  94. Ron says:

    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 for dealing with a change at a higher 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.
    Ron

    • Hi Ron,

      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. Excellent job!

  95. Neil says:

    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. e.g. if I choose A then the second list will only show values A1 to A5. and so on. Is this possible?

  96. David says:

    HI, Your tutorial was excellent for what I needed. One additional item. 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.

    Thanks
    David

  97. Alex says:

    Hi,
    I want to know if it is possible to put different lists in the same dropdown.

    Thanks,
    Alex

  98. Doreen says:

    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.

  99. Kash says:

    Thanks for the great tutorial.

    One additional function i need to know.
    First column will be DropDown List and second column just shows the specific date.

    Example.

    We have 5 items names

    A
    B
    C
    D
    E

    And Values as follow

    60
    70
    80
    90
    100

    So I want in the first column A DropDown List which should contains a list of (A,B,C,D,E) and When I select any one of them it should show the related value (A=60) (B=70) etc

    Thank You

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard