Comments on: How to make a dynamic dependent dropdown list in Excel an easy way

Creating a simple drop down list in Excel is easy. Making a cascading drop-down has always been a problem. But not any more! The introduction of dynamic arrays in Excel 365 provided a fast and easy solution. Continue reading →

Comments page 2. Total comments: 165

  1. Thanks for the insightful explainer.

    I noticed that if a cell is already populated, changing the adjacent cell where the data would now not match does not depopulate the cell, meaning the cell now has erroneous info. How would one automate the cell to exclude non conforming input?

    Thanks.

    1. Hi! If I understand correctly, you can automatically remove data from a cell with a second dropdown list when the first dropdown list is modified using a VBA macro.

  2. How can I achieve the same thing but with multiple rows in column D:E?
    Like: I have several rows where the user selects a value from the first drop-down list and the cell right to it needs to be dynamically populated with corresponding values.

    Your example works only if I have one row, because you will need one temporary table. How does it work with multiple rows of lists?

    1. I have the same question as Dennis, does anyone have the answer?
      Meaning: I want to perform the same cascading dynamic dropdown menu's multiple times, but this means that for each input row, I would have to make a unique temporary table. Can this be done easier? Can I put the UNIQUE and FILTER formula in the data validation list criteria?

      1. Hi! If I understand your task correctly, this article above may be helpful. This article shows you how to use the Dynamic Array functions to create a cascading drop-down list in Excel.

        1. Thanks Alexander! Unfortunately, this is not what I am looking for. I have a multiple dependent drop-down list as in your example, but I want to perform this exercise multiple times based on the same input table. Using the example your sent, this means that I want to be able to select fruit, then exporter, then state/province in cells E3:G3 (using the info from the preparation table), en then I want to perform this exercise again in cells E4:G4 and again and again. But this means taht I would have to build a new preparation table each time I want to perform this exercise right? Or is there a simpler way, eg by using the UNIQUE and FILTER formulas in the data validation list criteria?

          1. Have you read the article you are commenting on? The article talks about how to use UNIQUE and FILTER formulas in the data validation list criteria. Or explain why these recommendations do not work for you.

            1. Hi, yes I have read the article above. Probably I am not understanding it correctly, but as I read it, it explains how to use the UNIQUE and FILTER formulas to create the correct list in the preparation table. Then this list can be called in the data validation list. I have succeeded in doing so.
              However, my case requires something different. Let me explain:

              I have a table with headers called 'Supplier', 'Product Group', 'Product Name', 'Cost Price' and 'Discount'. I want users to be able to select their top 10-20 products in terms of procurement volume and see in one glance how much discount they can get on the total of these top products. This means that I want all of their top 10-20 products in an overview.
              Since the list of products is enormous, I want them to be able to first filter by supplier, then by productgroup, and then select their top product. Then automatically cost price and discount will be shown. Afterwards, the user has to be able to select their second top product in the row below. Selecting the supplier is no problem, but then selecting the product group is, because the drop-down list still refers to the selected supplier form the first product. Do I need to create a preparation table for the total number of products I want my users to be able to select? Or can I include the combi UNIQUE and FILTER formula in the data validation criteria (so not in the preparation table)?

              1. Hi! I can't see your data and can't give you a formula. I think it is better to create a separate dynamic table using the FILTER formula and get the data for the dropdown list from it.

      1. This worked perfectly. I had my spreadsheet with a list of businesses in different categories and those having their own sub-categories. Do the exercise on the page, don't try to immediately interpret it into your application. Just - fresh spreadsheet, apricots, oranges and Brazils. It makes complete sense when you do the exercise, and then easy to interpret into my application. Thanks, Alexander and Ablebits Team!

  3. Thank you for training

  4. thanks, it as a great help

  5. Sorry, new to validation, for some reason when I asked my last question, did not realize that Copy would work fine.

  6. I would like to create many (dozens or more) drop down lists, for many separate cells in many rows of a spreadsheet. Each drop down list is dynamic and based on a formula that I can probably make identical (or nearly identical) to all the others. For example, the formula for the pulldown list might be this:
    XLOOKUP($L$21, $Q$2:$Q$8,CHOOSECOLS($S$2:$AB$8,1,3,5,7,9),"ERR",0) where $L$21 (the variable entry) determines the entry I want to match to column Q, and the pulldown items are in some part of the columns in S-AB.

    I am pretty sure I could make the formula identical in each drop down with a little more work, perhaps by using the row number of the item being searched but I'm trying to avoid volatile functions so haven't worked on this yet.

    In any case, is there a way to "mass produce" drop down lists, either with an identical formula or better, a slightly variable one that can be copied down a column. It would save a lot of time if so. Thank you for the great work you do.

  7. Svetlana, it is a terrific blog, really helped me a lot !

  8. Thanks for the information! If I've created the dropdown lists, how would I automatically place the source formula for the remaining cells? It seems like if I copy and paste the =indirectC3 to the row the second set of data won't show up. I've been manually typing in =indirectc3, indirectc4, so on and so forth. How can I resolve that issue?

  9. Thanks for the tutorial. Just what I was looking for. The formula works for the first two columns of data, when I move to the 3rd, 4th, and 5th columns I am only getting the very top row of data from my table. Any idea how to correct this? Thanks

  10. Hi there,
    Thanks for the tutorial, it is very helpful. How to do the same in two different Excel sheets, I mean having the main table in the first sheet and the data source in the second sheet?

  11. Great article. Is there a way of concatenating all the functionality such that the "Preparation" columns are not necessary, and use it within a Data Validation dropdown menu?
    Thanks

    1. Hi! The Preparation columns are needed to create a drop-down list and have nothing to do with macros that allow you to select multiple values in that drop-down list. This is discussed in the first paragraph of the article.

  12. My spreadsheet has the data dropdowns figured out. The problem I am having is that on one computer, I start typing in a name from my table and I don't have to press the drop down button to get the matching names, it just starts dynamically showing the names as I am typing. On my work computer, with the exact same spreadsheet, I have to type in a little bit of a name and then I have to go to the right of the name and manually hit the drop down button. I don't think this has to do with the spreadsheet itself, but maybe a setting inside excel.

    Do you have experience with this?

    Dan

    1. Hi!
      You may be working with different versions of Excel. The formula used to create the drop-down list may work differently.

  13. Thank you for this tutorial! A follow-up question - is there a way to extend the number of rows for the dynamic drop-down section (ie have multiple rows with the dynamic drop-down list using the same source data)?

    1. Did you get any answer for this ?

      1. Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail.

  14. Thanks for this tutorial. It saved me a lot of time 😀

  15. This article was great, thanks.

  16. Hi,

    I have a master workbook containing a code list (B2:B2000) that gets updated throughout the year, currently with data only in cells B2:B15. I am setting up a template workbook with a data validation list referring to the code list, however because both the master workbook and template workbook need to be kept open for data validation list to work this is problematic. The template will be used by multiple users to create their personal workbook, hence why it is impractial to update the code list on all the user workbooks.

    As a workaround, in the template workbook I have referenced the list from the master workbook, and then set up the drop down list in the template using data validation. However, the drop down list now contains zeros for all of the cells in the master workbook that currently don't contain any data yet (B16:B2000). How do I go about removing the zeros from the list, seeing as I cannot a dynamic data validation list in this instance?

    Or there an better solution altogether in using a data validation list in a workbook that refers to a dynamic list in another workbook?

  17. What if I have multiple rows of drop downs?

  18. I am trying to create a drop down list where the depending on what date is input the corresponding drop list will appear. For example if a user adds a date 01/06/2022 1 drop down list will appear however, if the user adds the date 01/06/2021 a different drop down list will appear. -

    01/01/1900 to 31/03/2021 01/04/2021 to 24/11/2021 25/11/2021 to 31/03/2022 01/04/2022 to 31/03/2023 (Date range)
    2020/2021 2021 DEC 2021/2022 2022/23 (Drop down list title)

    £292.00 £293.00 £335.00 £344.00 Drop down list values)
    £512.00 £515.00 £557.00 £573.00
    £0.00 £0.00 £0.00 £0.00

    I hope that the 'table' makes sense as to what I am trying to do.

  19. This tutorial worked brilliantly. Thank you so much for sharing this information. I have been able to setup dependent drop down list for three data collection points. I did run into one problem. I think I may have missed something. While I was able to get it to work in the initial cells that I have setup I can no make the formula apply to the entire column despite special pasting the formula. I was wondering how to get the dependent drown down list to work for multiple rows?

    Any help would be appreciated. Thank you so much.

  20. Great tutorial, as so many of your others, thank you! This tutorial touches on what I'm looking for, but I think I'm missing something. Here's my scenario; I have a list/table of Vendors in cells F5:F10, I have their corresponding Addresses in cells G5:G10. In the same spreadsheet in cell C33 I have a dropdown menu of Vendors (from cells F5:F10), and I would like the Address (from cells G5:G10), that corresponds to the selected Vendor, to auto populate in cell D33. Your help would be greatly appreciated!

  21. Hi, I can’t seem to find a good solution for wanting to have my list for drop down list in another sheet AND be dynamic so the user can add or delete and it will automatically adjust the drop down. So, sheet 1 has a column, sheet 2 has a column with the valid names that the column in sheet 1 will display. If the user deletes a cell, and/or adds a cell in the sheet 2 list it will be reflected in the drop down on sheet 1.

    I’ve tried making sheet 2 column a table, naming it, and data validation in sheet one but I can’t get it to refer to the table in another sheet. Or when I get close it won’t update.

    Lastly, even manually I cannot just delete a cell in a table, or I can but it leaves a blank. The user will be confused by this. Any help at all appreciated.

      1. Hey, thanks and WOW, quick response! I will check those out, thanks very much!

  22. What if you had another sheet that had First Name, Last Name, Email, Fruit, Exporters, State. The first 3 columns are text, but the last 3 you want validation to get the drop-downs for your source data like you had in this article. This article based it on one drop-down for which the preparation table was based on the users selection for that one drop-down. In my example, each row would be a different drop-down, which results in way too many preparation table lists.

    FirstName | LastName | Email | Fruit | Exporters | State
    Bob Smith @ Apricot
    Lisa Swoth @ Mango
    Joe Szath @ Mango
    Jill Trast @ Orange

  23. Hi, I was wondering if is there a ways for the dropdown list expand when we add more items to the full list? Automatically of course! Thanks in advance

    1. Hi Dan,

      Please see the "How to make an expandable drop down list in Excel" section in this tutorial :)

  24. Hello! Is it possible to select more than one value from the dropdown? Using your example, if I want to indicate that I used Apricot Exporters from both Algeria and Pakistan, how could I select both countries from the same dropdown and have both countries appear in the cell? Note that in this instance, I would only want to select two of the four countries -- I would not want Iran or Turkey to be "selected" from the dropdown. Thanks!

  25. Hi There,

    Thank you so much for your guide, it is very easily to follow. How can I have multiple drop downs that rely on each other for results, instead of just from left to right? For example, your Country column is dependent on the Exporter column. and that depends on the Fruit column. Is there a formula where I can select Country or Exporter, and it would filter down the Exporter and/or Fruit columns?

    Thank you

      1. So they don't necessarily replace slicer/filters?

  26. I am trying to create a continuing drop down, where after you make a selection that selection is removed and it is not available in the next drop down. For example you have a list of Minnesota, Illinois, Iowa, Missouri in column A. In B you create the drop down containing those states. I C i want whatever state chosen in B to not be available in that drop down and so on.

  27. Hi there, I have managed to get this working but my aim is to allow dropdowns on every row for people to use with data collection. When I drag the data validation down and I go to the dropdown in next row down it still references first row selection. How can I make a data entry drop down list for hundreds of rows that each are independent per row?

  28. Hello,

    I've tried the steps you've provided for a dependent drop down for my data and keep getting stuck at the second point with the filter= formula

    I am trying to create three drop downs, dependent on three levels. For example:

    Video Surveillance Accessories Illuminators
    Video Surveillance Analog Cameras Panoramic Cameras
    Video Surveillance Analog Cameras Body Cameras
    Video Surveillance Analog Cameras Box Cameras
    Video Surveillance Analog Cameras Bullet Cameras
    Video Surveillance Analog Cameras Covert Cameras
    Video Surveillance Analog Cameras Dome Cameras
    Video Surveillance Analog Cameras PTZ Security Cameras
    Video Surveillance Analog Cameras Thermal Imaging Cameras
    Video Surveillance Analog Cameras Turret Cameras
    Video Surveillance Analog Cameras Mobile & Vehicle Dash Cameras
    Video Surveillance Analog Cameras Wireless Cameras
    Video Surveillance Camera Lenses Fixed Lenses
    Video Surveillance Camera Lenses Varifocal Lenses
    Video Surveillance Camera Lenses Parts & Accessories
    Video Surveillance Dummy Cameras
    Video Surveillance HDoC Cameras Panoramic Cameras
    Video Surveillance HDoC Cameras Body Cameras
    Video Surveillance HDoC Cameras Box Cameras
    Video Surveillance HDoC Cameras Bullet Cameras
    Video Surveillance HDoC Cameras Covert Cameras
    Video Surveillance HDoC Cameras Dome Cameras
    Video Surveillance HDoC Cameras PTZ Security Cameras
    Video Surveillance HDoC Cameras Thermal Imaging Cameras
    Video Surveillance HDoC Cameras Turret Cameras
    Video Surveillance HDoC Cameras Mobile & Vehicle Dash Cameras
    Video Surveillance HDoC Cameras Wireless Cameras

    So, if they select Video Surveillance, all of the options from the second column would be available.. and then once they select the second column, only the options that are associated with that level would appear. Can someone help me?

  29. What code will i get if i have 2 dropdown list in the same table:
    ex:
    BUILDING AGE i (iii,ii) (iv-a,iv-b) (v-a,v-b,v-c)
    3 Years 8.90% 6.90% 5.60% 4.20%
    4 Years 11.70% 9.00% 7.40% 5.50%
    5 Years 14.50% 11.10% 9.10% 6.70%
    6 Years 17.20% 13.10% 10.70% 8.10%
    Question:
    if i have dropdown list (Building age) and (i * iii,ii * iv-a,iv-b * v-a,v-b,v-c)
    what code for the target 5.60% if i choose 3yrs(building age) and (iv-a,iv-b) .

  30. How to get your target for two dropdown in the same table
    ex:
    YEAR NAME % fruit
    25 juan 5
    3 maria 7
    50 leo 9
    IF i have dropdown list of (YEAR) and (NAME&PERCENT), what will be the code if i click *3 (year) and *

  31. High,

    I have a situation in hand that requires me to create a solution for the example below:

    I nedd to prepare proposals for drywall jobs. I need to be able to alternate between various thickness of insulation, and each thickness have its price.
    I would like to be able to have a dropdown list where the prices would adjust with each choide of thickness made.
    Could you help?
    Thanks

    1. Hello!
      Create a drop-down list to select the thickness. Then create a dependent dropdown list for the price as described in the article above. Also, instead of the second dependent drop-down list, you can use the search for the desired price using the VLOOKUP function.

      1. XLOOKUP :)

  32. Thank you for the great instructions! Do you have a solution that would clear, refresh or otherwise revert a drop-down back to a specific cell (i.e. Choose from Dropdown) when one of the drop-downs are selected that changes the filtered criteria?

    For example, if the current drop-downs selected are: Orange > USA > California then the user changes the first drop-down to Apricot I would like the two subsequent drop-downs to clear, refresh or show a specific message (i.e Choose from Dropdown) instead of still showing USA and California.

    Thank you,

    1. Hello!
      If you create a drop-down list using regular Excel tools, it will not automatically change.
      To solve your problem, you need to use a VBA macro.

      1. Thank you for the reply!

  33. Hi there,

    Thank you for making this it is exactly what I am looking for. I am attempting to follow "How to create multiple dependent drop down list in Excel". When I get to point 2 "=UNIQUE(FILTER(B3:B15, A3:A15=E3))" I get the error code "empty array and #CALC!". Any ideas on how to fix this please?

    1. Hello!
      I can't see your data, but I'm assuming the FILTER function is returning an empty array. That is, there is no value that matches the filter criteria. Try changing your formula to something like this:

      =UNIQUE(FILTER(B3:B15, A3:A15=E3,"No results"))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Hi Alexander,

        Thank you so much for your reply! This has fixed the issue. I really appreciate the help! I have spent weeks trying to sort this!

        Thanks,

        Lisa

  34. Thank you so much for putting this explanation on here.
    I have a question though:
    I have a table in Sheet 1 which has order details from a vendor
    I have a separate sheet for a list of products which has a column for the name of the vendor from whom they are sourced. So I can make a dependant list based on the instructions given above.
    The problem I run into, is that each row in the table on Sheet 1 could have a different vendor so its not one instance of the data validation / list. So unless I can enter the filter function into the range field of the data validation where it would be sorting for each row based on the vendor selected in that row (for that purchase order), how would this work?

    To explain it in another way, I am making a table with all the purchase orders for this month. As I create a new purchase order in a new row, I select a vendor and would like to see a filtered list of products which I buy from this vendor only. Each row could have a different vendor, so a static single instance of using filter and referencing that range for the drop down might not work right?

    1. Hello!
      To list only products from the selected vendor, use the dependent dropdown list as described in this article above.

  35. Hii need some help. Suppose I create a column lke below:
    Fruits
    Vegeis
    Snacks
    Milk
    Eggs
    Now I"ll assign these to a dropdown list(1). But I want to make further more lists linked to some of thse... like a dropdown list for fruits(2) with options say apple mango papaya; same for vegies(3) with spinach crrot potato and last for snacks()4 with chips, cold drinks.
    Meaning to say that in list 1, when I select fruit so I shall be able to select one of the items from list 2, if I select vegies so an item from list 3. So bit confused, how can I achievethis!? Pls guide me. thank you in advance.

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

  36. Hi, how can i create a formula that arranges automatically, the initial date and final date that a numerical value ( a rate) is present on a list.

    something li ke this
    Date: Rate: From: To:
    01/01/2021 300 01/01/2021 06/01/2021 300
    02/01/2021 300 07/01/2021 07/01/2021 400
    03/01/2021 300 08/01/2021 10/01/2021 120
    04/01/2021 300 11/01/2021 17/01/2021 700
    05/01/2021 300
    06/01/2021 300
    07/01/2021 400
    08/01/2021 120
    09/01/2021 120
    10/01/2021 120
    11/01/2021 700
    12/01/2021 700
    13/01/2021 700
    14/01/2021 700
    15/01/2021 700
    16/01/2021 700
    17/01/2021 700

  37. Thanks for this solution.

    A quick question ...

    Is it possible to refresh the second dropdown when the first is updated?

    For example, if you choose Apricot and Algeria; and then subsequently select 'Orange' the second box doesn't refresh until you click on the dropdown and select the new exporter.

    Is it possible to refresh the second box when the first is updated, either to select the first in a newly filtered list or to change this to a blank selection?

    Unfortunately, I'm thinking that the answer may need to be in VBA ....

    1. I am also looking for an answer to this scenario, can anybody help. For me the scenario is In column 1 I have a Project Code and in Column B I have a list of task that are part of each project code. If I go ahead with the dropdown, it only get updated in the referred cell ie A13.
      =FILTER(Table01[Project Task],Table01[Project Code]='TS_APR 22_JC'!A13)

      So A13 I select the Project Code and in B13 I can see the relevant task in the dropdown
      BUT
      When I go to A14 and select another Project Code B14 does not change, it still shows the list that is found in B13

  38. Hi,

    I need some help. I have a text file contains ServerName: ServerA, under serverA is a list of software names, and the list of server name and software name goes on and on.

    1/What I'd like to do is to read data from the text file and display in rows with cols....like this:

    Ex: ServerName: ServerA----------ServerName: ServerB----------ServerName:Server:ServerC
    nameofsw--------------------------nameofsw-------------------------nameofsw
    nameofsw--------------------------nameofsw-------------------------nameofsw
    etc.............................................etc...........................................etc
    until there's no more ServerName and no more software names.

    2/How do I create a look up by software name and it will return the number of ServerNames?

    Thank you very much,

  39. Hello, this was just the information I was looking for! Very helpful instructions and easy to follow. What if you have a 4th dependent drop down, or 5th, 6th, etc.? Could you explain how to construct the formula for the 4th column in the preparation table?

    1. Hi Tim.

      Simply, add more criteria to the FILTER function. Also, use the UNIQUE function for all the previous drop-downs, so they contain only one instance of each item.

      In the "Create a multiple dependent drop down list" example:

      The formula to get the items for 3rd dropdown (G8 in the preparation table) would be:

      =UNIQUE(FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3)))

      The formula to get the items for 4rd dropdown (H8 in the preparation table) would be:

      =UNIQUE(FILTER(D3:D15, (A3:A15=E3) * (B3:B15=F3) * (C3:C15=G3)))

      Where D3:D15 are the source data for the 4th drop-down.

  40. Hi, Love your instructions, very easy to understand and follow. I've stumbled upon a bit of a road block with a form I'm trying to create where the user may want to select items in a slightly different way than expected.
    For example:
    Two dropdowns - SYSTEM and PART NUMBER
    If the user selects an item from the SYSTEM dropdown, the relevant Part numbers will show in the PART NUMBER dropdown.
    However, if the user selects the PART NUMBER dropdown, they can choose from ANY of the Part numbers available and then the SYSTEM dropdown will show the applicable items. so it's kind of dependent both ways?
    I'm not sure how I would even start to go about this or even what I would need to search for... any advice?

      1. Hi Alexander,

        Thanks for the reply. I'm afraid I must not have explained myself properly.... I understand the sample and can replicate that just fine. My question is ... what if the user wants to select the country first, then the fruit or select the fruit and then the country. The dropdowns would be dependent two ways (2 then 1, 1 then 2, etc) not just drop down 1, then 2, then 3, etc. How would you do that on one form?

        1. Hi,
          A dropdown list can only have one data source. Therefore, your task cannot be solved by standard Excel tools. You can change the data source for the drop-down list using a VBA macro if you use a Combo Box.

  41. Does this solution immediately remove the second drop down menu if you change the first one? Or do you still need to use VBA to clear the cell? I don't get VBA to work.. so I'm looking for another solution!

  42. Your instructions are clear, very well written and quite easy to follow. Thank you! The was exactly what I've been searching for this afternoon. It took me all of a few minutes to set this up on my spreadsheet.

    However I, like several commenter here, are wondering how to get this to work for multiple rows. Dropdown lists like these are most often used in spreadsheets with dozens or hundreds of rows to facilitate data entry.

    This only seems to work for one dropdown list in D3 and the dependent list works only in E3.

    How do you make this work for cells D4/E4, D5/E5, D6/E6 etc? Adding the List in Data Validation to the succeeding rows is easy. Making the list change in column H to reflect the value selected in D4 just seems to have no answer.

    =FILTER(B3:B20, A3:A20=D3) the sticking point in the formula is the D3 designation at the end. Nothing I've tried so\r far works.

    Any insight you could give would be greatly appreciated.

    Thank you!

    2. Hi Julie,

      Unfortunately, there is no easy way to get this FILTER formula to work for multiple rows. However, a solution does exist and we will describe it in a separate article after the holidays.

      In the meantime, you can create a dynamic cascading drop-down list in the old-fashioned way by using Excel tables. Please pay special attention to the tip that explains the importance of using appropriate cell references to allow drop-downs to copy to other cells correctly.

  43. Excellent post and super easy to follow. Works great when only needing one row of dynamic dropdown data

    I think my question has been asked above, but let me restate it a different way:

    How do I make the last argument =D3 portion of =FILTER(B3:B20,A3:A20=D3) dynamic??

    I’d like to drag or copy the picklists for Fruit and Exporter, down multiple rows.
    I.e. Mango chosen in D3 shows the picklist of Mango Exporters in E3;
    Apricot chosen in D4, should show picklist of Apricot Exporters in E4;
    Orange chosen in D5, should show picklist of Orange Exporters in E5;
    etc….on down for 10+ rows.

    The Exporters (col. H) on the Preparation table continues to look to D3 for the Fruit filter of Mango, rather than updating to the next row's pick (D4) of Apricot.
    Is there a way to make the FILTER function dynamic?

    Thank you for sharing this great post!

    2. Very well stated @Lori. Same question!!! Please help.

  44. I can not get the third box to work within my set up with the formula the other two boxes work and I have followed the instructions, and it is only registering one of the text boxes, not all of them

  45. The question was raised, how to solve the following case:
    I am not talking about the multilevel dropdown list but the first variant with a filtered lookup value:

    In your sample, the dynamic drop-down table with the header Fruit and Exporter has one single row. And you solution works just fine. Now, I want to add another row D4:E4.

    I pick a new value from the dropdown list in cell D4. Works just fine. Now in cell Eç, the dropdown list still refers to cell H3, which of course is unchanged and still filters the list based on the value in cell D3. So I could not figure out yet a decent solution for multi row tables

  46. Hi, I am trying to set up a navigation page where I can sort over several sheets. I've tried several things but nothing seems to work. An example of what I am trying to do is below: are you able to assist???

    Sort Monthly Trackable Report

    Section
    Sort Business Development

    Projects
    Section Manager Branch Budget PM Status

  47. Hi, Thank you for the instructions on how to create multiple dependant drop down lists. I managed to create my own lists on my training spreadsheet, however this would only work where I have a single row of drop down entries. How would I be able to resolve manage multiple rows where I need to collect information for multiple columns from the multiple dependant drop down lists?

    1. Hi,
      Your task is not completely clear to me. However, a dropdown list can only fit a value into one cell. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

  48. Hello
    When I have the table and the primary & dependent drop-downs on the same sheet, it works fine
    However, when I have the table on sheet-2 and the primary & dependent drop-downs on sheet-1, I get a source error on dependent dropdown validation cell (the dropdown works fine)
    So, can the dependent dropdown list be on separate sheet from the table?
    Thank you

    1. Hello!
      the dependent dropdown list can be on another sheet. The data link must contain the sheet name:

      =Sheet1!B1:B10

      I hope my advice will help you solve your task.

      1. Yes the sheet name is there
        Okay, thank you

  49. Does excel 2016 has Unique function?? If not, how would I get unique values in dropdown

    1. Hello!
      This function is currently available to Microsoft 365 subscribers in Current Channel. It will be available to Microsoft 365 subscribers in Semi-Annual Enterprise Channel starting in July 2020. We have a special article that shows how to find uniques.

  50. Thank you a lot!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)