Top List List

Dependent drop down list different sheet

Sep

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

by | updated on November 1, 2021

The tutorial shows how to create an Excel drop down list depending on another cell by using new dynamic array functions.

Creating a simple drop down list in Excel is easy. Making a multi-level cascading drop-down has always been a challenge. The above linked tutorial describes four different approaches, each including a crazy number of steps, a bunch of different formulas, and a handful of limitations relating to multi-word entries, blank cells, etc.

That was the bad news. The good news is that those methods were designed for pre-dynamic versions of Excel. The introduction of dynamic arrays in Excel 365 has changed everything! With new dynamic array functions, creating a multiple dependent drop-down list is a matter of minutes, if not seconds. No tricks, no caveats, no nonsense. Only fast, straightforward and easy-to-follow solutions.

Make a dynamic drop down list in Excel
Create a multiple dependent drop down list
Create an expandable dropdown excluding blank cells
Sort drop down list alphabetically

Notes:

This new dynamic array way of making dropdown lists only works in Excel 365 and Excel 2021. In pre-dynamic Excel, you will have to do it the long old-fashioned way as described in Creating a dependent drop down in Excel 2019, 2016 and earlier.
This solution is for a single row. If you want to copy your picklists down multiple rows, then follow the instructions in Dependent drop-down list for multiple rows.

How to make dynamic drop down list in Excel

This example demonstrates the general approach to creating a cascading drop down list in Excel by using the new dynamic array functions.

Supposing you have a list of fruit in column A and exporters in column B. An additional complication is that the fruit names are not grouped but scattered across the column. The goal is to put the unique fruit names in the first drop-down and depending on the user's selection show the relevant exporters in the second drop-down.

To create a dynamic dependent drop down list in Excel, carry out these steps:

1. Get items for the main drop down list

For starters, we shall extract all different fruit names from column A. This can be done by using the UNIQUE function in its simplest form - supply the fruit list for the first argument [array] and omit the remaining optional arguments as their defaults work just fine for us:

=UNIQUE[A3:A15]

The formula goes to G3, and after pressing the Enter key the results spill into the next cells automatically.

2. Create the main drop down

To make your primary drop-down list, configure an Excel Data Validation rule in this way:

Select a cell in which you want the dropdown to appear [D3 in our case].
On the Data tab, in the Data Tools group, click Data Validation.
In the Data Validation dialog box, do the following:
- Under Allow, select List.
- In the Source box, enter the reference to the spill range output by the UNIQUE formula. For this, type the hash tag right after the cell reference, like this: =$G$3#
  This is called a spill range reference, and this syntax refers to the entire range regardless of how much it expands or contracts.
- Click OK to close the dialog.

Your primary drop-down list is done!

3. Get items for the dependent drop down list

To get entries for the secondary dropdown menu, we'll filter the values in column B based on the value selected in the first dropdown. This can be done with the help of another dynamic array function called FILTER:

=FILTER[B3:B15, A3:A15=D3]

Where B3:B15 are the source data for your dependent drop down, A3:A15 are the source data for your main dropdown, and D3 is the main dropdown cell.

To make sure the formula works correctly, you can select some value in the first drop-down list and observe the results returned by FILTER. Perfect! :]

4. Make the dependent drop down

To create the second dropdown list, configure the data validation criteria exactly as you did for the first drop down at step 2. But this time, reference the spill range returned by the FILTER function: =$H$3#

That's it! Your Excel dependent dropdown list is ready for use.

Tips and notes:

To have the new entries included in the drop-down list automatically, format your source data as an Excel table. Or you can include a few blank cells in your formulas as demonstrated in this example.
If your original data contains any gaps, you can filter out blanks by using this solution.
To alphabetically sort a dropdown's items, wrap your formulas in the SORT function as explained in this example.

How to create multiple dependent drop down list in Excel

In the previous example, we made a drop down list depending on another cell. But what if you need a multi-level hierarchy, i.e. a 3rd dropdown depending in the 2nd list, or even a 4th dropdown depending on the 3rd list. Is that possible? Yes, you can set up any number of dependent lists [a reasonable number, of course :].

For this example, we have placed states / provinces in column C, and are now looking to add a corresponding dropdown menu in G3:

To make a multiple dependent drop down list in Excel, this is what you need to do:

1. Set up the first drop down

The main dropdown list is created with exact the same steps as in the previous example [please see steps 1 and 2 above]. The only difference is the spill range reference you enter in the Source box.

This time, the UNIQUE formula is in E8, and the main drop down list is going to be in E3. So, you select E3, click Data Validation, and supply this reference: =$E$8#

2. Configure the second drop down

As you may have noticed, now column B contains multiple occurrences of the same exporters. But you want only unique names in your dropdown list, right? To leave out all duplicate occurrences, wrap the UNIQUE function around your FILTER formula, and enter this updated formula in F8:

=UNIQUE[FILTER[B3:B15, A3:A15=E3]]

Where B3:B15 are the source data for the second drop down, A3:A15 are the source data for the first dropdown, and E3 is the first dropdown cell.

After that, use the following spill range reference for the Data Validation criteria: =$F$8#

3. Set up the third drop down

To gather the items for the 3rd drop down list, make use of the FILTER formula with multiple criteria. The first criterion checks the entire fruit list against the value selected in the 1st dropdown [A3:A15=E3] while the second criterion tests the list of exporters against the selection in the 2nd dropdown [B3:B15=F3]. The complete formula goes to G8:

=FILTER[C3:C15, [A3:A15=E3] * [B3:B15=F3]]

If you are going to add more dependent dropdowns [4th, 5th, etc.], then most likely column C will contain multiple occurrences of the same item. To prevent duplicates from getting into the preparation table, and consequently in the 3rd dropdown, nest the FILTER formula in the UNIQUE function like we did in the previous step:

=UNIQUE[FILTER[C3:C15, [A3:A15=E3] * [B3:B15=F3]]]

The last thing for you to do is to create one more Data Validation rule with this Source reference: =$G$8#

Your multiple dependent drop down list is good to go!

Tip. In a similar manner, you can get items for subsequent drop-downs. Assuming column D contains the source data for your 4th dropdown list, you can enter the following formula in H8 to retrieve the corresponding items:

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

How to make an expandable drop down list in Excel

After creating a dropdown, your first concern may be as to what happens when you add new items to the source data. Will the dropdown list update automatically? If your original data is formatted as Excel table, then yes, a dynamic drop down list discussed in the previous examples will expand automatically without any effort on your side because Excel tables are expandable by their nature.

If for some reason using an Excel table is not an option, you can make your dropdown list expandable in this way:

To include new data automatically as it is added to the source list, add a few extra cells to the arrays referenced in your formulas.
To exclude blank cells, configure the formulas to ignore empty cells until they get filled.

Keeping these two points in mind, let's fine-tune the formulas in our data preparation table. The Data Validation rules do not require any adjustments at all.

Formula for the main dropdown

With the fruit names in A3:A15, we add 5 extra cells to the array to cater for possible new entries. Additionally, we embed the FILTER function into UNIQUE to extract unique values without blanks.

Given the above, the formula in G3 takes this shape:

=UNIQUE[FILTER[A3:A20, A3:A20""]]

Formula for the dependent dropdown

The formula in G3 does not need much tweaking - just extend the arrays with a few more cells:

=FILTER[B3:B20, A3:A20=D3]

The result is a fully dynamic expandable dependent drop down list:

How to sort drop down list alphabetically

Want to arrange your dropdown list alphabetically without resorting the source data? The new dynamic Excel has a special function for this too! In your data preparation table, simply wrap the SORT function around your existing formulas.

The data validation rules are configured exactly as described in the previous examples.

To sort from A to Z

Since the ascending sort order is the default option, you can just nest your existing formulas in the array argument of SORT, omitting all other arguments which are optional.

For the main dropdown [the formula in G3]:

=SORT[UNIQUE[FILTER[A3:A20, A3:A20""]]]

For the dependent dropdown [the formula in H3]:

=SORT[FILTER[B3:B20, A3:A20=D3]]

Done! Both drop down lists get sorted alphabetically A to Z.

To sort from Z to A

To sort in descending order, you need to set the 3rd argument [sort_order] of the SORT function to -1.

For the main dropdown [the formula in G3]:

=SORT[UNIQUE[FILTER[A3:A20, A3:A20""]], 1, -1]

For the dependent dropdown [the formula in H3]:

=SORT[FILTER[B3:B20, A3:A20=D3], 1, -1]

This will sort both the data in the preparation table and the items in the dropdown lists from Z to A:

That's how to create dynamic drop down list in Excel with the help of the new dynamic array functions. Unlike the traditional methods, this approach works perfectly for single and multi-word entries and takes care of any blank cells. Thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel dependent drop down list [.xlsx file]

You may also be interested in

How to create, edit and delete dropdown list in Excel
Create a multiple dependent drop down list in Excel 2019 - 2010
Make an expandable dependent drop-down list for multiple rows

Excel: featured articles

Merge multiple sheets intoone
Combine Excel files into one
Compare two files / worksheets
Merge 2 columns in Excel
Compare 2 columns in Excel for matches and differences
How to merge two or more tables in Excel
CONCATENATE in Excel: combine text strings, cells and columns
Create calendar in Excel [drop-down and printable]
3 ways to remove spaces between words in Excel cells

47 comments to "How to make a dynamic dependent dropdown list in Excel an easy way"

Chris says:
January 5, 2022 at 4:52 pm
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,
Reply
- Alexander Trifuntov [Ablebits Team] says:
  January 6, 2022 at 8:24 am
  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.
  Reply
  - Chris says:
    January 6, 2022 at 5:41 pm
    Thank you for the reply!
    Reply
Lisa Mcdade says:
December 13, 2021 at 3:57 pm
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?
Reply
- Alexander Trifuntov [Ablebits Team] says:
  December 14, 2021 at 7:23 am
  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.
  Reply
  - Lisa Mcdade says:
    December 15, 2021 at 3:57 pm
    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
    Reply
Adi says:
November 14, 2021 at 6:52 pm
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?
Reply
- Alexander Trifuntov [Ablebits Team] says:
  November 15, 2021 at 8:27 am
  Hello!
  To list only products from the selected vendor, use the dependent dropdown list as described in this article above.
  Reply
Andy says:
October 5, 2021 at 7:21 pm
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.

Reply
- Alexander Trifuntov [Ablebits Team] says:
  October 11, 2021 at 12:30 pm
  Hi!
  I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
  Reply
Memo says:
August 2, 2021 at 7:53 pm
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
Reply
- Alexander Trifuntov [Ablebits Team] says:
  August 3, 2021 at 9:15 am
  Hello!
  If I understood the problem correctly, you can first filter the records you want and then sort them. I recommend reading this guide: Sort and filter in Excel.
  Reply
IanH says:
May 11, 2021 at 1:56 pm
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 ....
Reply
TB says:
April 21, 2021 at 7:42 pm
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,
Reply
Tim says:
March 16, 2021 at 5:24 pm
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?
Reply
- Svetlana Cheusheva [Ablebits Team] says:
  April 9, 2021 at 2:37 pm
  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.
  Reply
Melissa says:
February 11, 2021 at 4:13 pm
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?
Reply
- Alexander Trifuntov [Ablebits Team] says:
  February 12, 2021 at 10:29 am
  Hello!
  I think you have incorrectly specified the data for the dropdown list.
  Pay attention to the following paragraph of the article above Create a multiple dependent drop down list
  You can download the Practice workbook
  Pay attention to the sheet "Multiple dropdown".
  Reply
  - Melissa says:
    February 17, 2021 at 2:37 pm
    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?
    Reply
    - Alexander Trifuntov [Ablebits Team] says:
      February 17, 2021 at 3:13 pm
      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.
      Reply
Toon says:
January 12, 2021 at 5:13 pm
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!
Reply
Julie says:
December 22, 2020 at 11:00 pm
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!
Reply
- Svetlana Cheusheva [Ablebits Team] says:
  December 23, 2020 at 1:53 pm
  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.
  Reply
- Svetlana Cheusheva [Ablebits Team] says:
  January 20, 2021 at 11:55 am
  Update: A solution for a depended drop-down in multiple rows is published, hope you'll like it :]
  //www.ablebits.com/office-addins-blog/2021/01/20/dependent-drop-down-list-multiple-rows-excel-dynamic-arrays/
  Reply
Lori says:
December 18, 2020 at 6:38 pm
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??
Id 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!
Reply
- Sarah says:
  December 22, 2020 at 8:43 pm
  Very well stated @Lori. Same question!!! Please help.
  Reply
- Svetlana Cheusheva [Ablebits Team] says:
  January 20, 2021 at 12:07 pm
  Hi Lori,
  Thank you for the detailed description of your needs, it was really helpful! We have published a solution for multiple rows in a separate tutorial:
  Multiple rows dependent drop-down list in Excel
  Better late than never, eh? :]
  Reply
Katie says:
December 10, 2020 at 2:05 am
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
Reply
MikeH says:
December 5, 2020 at 12:06 pm
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
Reply
Hayley Dellaca says:
December 3, 2020 at 5:26 pm
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
Reply
Tarl Sagoo says:
November 18, 2020 at 11:10 pm
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?
Reply
- Alexander Trifuntov [Ablebits Team] says:
  November 19, 2020 at 7:54 am
  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. Itll help me understand it better and find a solution for you.
  Reply
JackC says:
November 18, 2020 at 8:38 pm
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
Reply
- Alexander Trifuntov [Ablebits Team] says:
  November 19, 2020 at 7:27 am
  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.
  Reply
  - JackC says:
    November 19, 2020 at 2:57 pm
    Yes the sheet name is there
    Okay, thank you
    Reply
Pooja says:
November 2, 2020 at 2:27 pm
Does excel 2016 has Unique function?? If not, how would I get unique values in dropdown
Reply
- Alexander Trifuntov [Ablebits Team] says:
  November 3, 2020 at 6:58 am
  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.
  Reply
Antony Pihut says:
October 30, 2020 at 6:19 pm
Thank you a lot!
Reply
Ian Wainwright says:
October 27, 2020 at 6:19 pm
Hi Svetlana. I really enjoyed the post.
Currently, when you select another top-level item [fruit] the sub-lists may display invalid options - ie, where the exporter does not deal in that fruit category. Is there a simple way to clear the sub-list selections or is this a job for vba?
Reply
- name says:
  December 23, 2020 at 2:48 pm
  I would like to know this as well!
  Reply
JM says:
October 27, 2020 at 2:04 pm
Hi, the same question like Mario M and YW. How to make multiple rows in drop-down table
Reply
- Alexander Trifuntov [Ablebits Team] says:
  October 27, 2020 at 2:34 pm
  Hello!
  If you mean the multilevel dropdown list where you can see the dependant values when hovering a cursor on one of the entrances, unfortunately, such functionality is not available in Excel. If however, this is not exactly what you need, please clarify. Thank you.
  Reply
YW says:
October 14, 2020 at 2:32 am
Hi, what if i have multiple rows in for the dynamic drop-down? all based on the same set of source table and dependent dropdown? how should i expand to dynamic drop down?
Reply
- Svetlana Cheusheva [Ablebits Team] says:
  January 20, 2021 at 12:04 pm
  Hello!
  The solution described on this page only works for a single row. But because many people here asked for a multiple rows dropdown, we slightly changed the approach [a bit more complex formulas for the preparation table and a dependent drop-down are needed], and published the solution in a separate tutorial:
  Create a dependent drop-down list for multiple rows
  Reply
Mario M says:
September 30, 2020 at 10:11 am
Good post. But hod do you do in case of multi level dependendant drop down menu in case you want to have more that one entry row? I mean. The example shows only three cells where to use the drop down menus. If you want to have a matrix type 3*3? Best
Reply
- Curious says:
  May 21, 2021 at 8:07 am
  I second this.
  I need to create a user generated table from a controlled data source.
  In the example above, if the table [D3:E3] is extended for a second input [D3:E4] and the second row Fruit is set to Apricot, how can the second dropdown be dynamically recalculated without having to create another helper column? This would grow to an enormous lookup table for larger data sets!
  Thanks
  Reply
xaerie says:
September 30, 2020 at 6:49 am
In Sheet 1 I have data validation/dropdown list from a range of cells in Sheet 2. I'm trying to find if it's possible to calculate costs in Sheet 1 depending on what's been selected from the dropdown in DataValidation. I've pasted the specific examples here:
Column1 Column2 Column3 Column4 Column5
1 text1 number1 number01 DataValidation1 Calculation based on DataValidation chosen in Column4
2 text2 number2 number02 DataValidation2 Calculation based on DataValidation chosen in Column4
3 text3 number3 number03 DataValidation3 Calculation based on DataValidation chosen in Column4
4 text4 number4 number04 DataValidation4 Calculation based on DataValidation chosen in Column4
5 text5 number5 number05 DataValidation5 Calculation based on DataValidation chosen in Column4
A B
1 DataValidation1 Formula for DataValidation1 to calculate costs. Example: [B2*4]*0.0029 + [B2*1+C2*29]*0.0012 + [B2 * 0.0015]
2 DataValidation2 Formula for DataValidation2 calculate costs. Example: [B2*4]*0.0029 + [B2*1+C2*29]*0.0012 + [C2 * 0.0015]
3 DataValidation3 Formula for DataValidation3 calculate costs. Example: [B2*4]*0.0029 + [B2*1+C2*29]*0.0012
4 DataValidation4 Formula for DataValidation4 calculate costs. Example: [B2*1]*0.0029 + [C2*29]*0.0012
5 DataValidation5 Formula for DataValidation5 calculate costs. Example: [B2*1]*0.01
another example:
A B C D E
1 Type Capacity Weight Classification Cost
2 Beam 12 1200 1:29 Calculation based on DataValidation chosen in D2
3 Other 8 850 1:4 Calculation based on DataValidation chosen in D3
4 Electronics 95 5240 1:29;1:4 Calculation based on DataValidation chosen in D4
5 Tools 4 900 1:29;1:4:1:1 Calculation based on DataValidation chosen in D5
6 Vehicle 3 20000 1:1 Calculation based on DataValidation chosen in D6
A B
1 DataValidation1 Formula for DataValidation1 to calculate costs. Example: [B2*4]*0.0029 + [B2*1+C2*29]*0.0012 + [B2 * 0.0015]
2 DataValidation2 Formula for DataValidation2 calculate costs. Example: [B2*4]*0.0029 + [B2*1+C2*29]*0.0012 + [C2 * 0.0015]
3 DataValidation3 Formula for DataValidation3 calculate costs. Example: [B2*4]*0.0029 + [B2*1+C2*29]*0.0012
4 DataValidation4 Formula for DataValidation4 calculate costs. Example: [B2*1]*0.0029 + [C2*29]*0.0012
5 DataValidation5 Formula for DataValidation5 calculate costs. Example: [B2*1]*0.01
I don't know if those formulas are going to work but those are the calculations I'm needing to make. A bunch of multiplying and adding in order.
Reply