creating dependend drop down lists

[tuspilica]

Hello friends,


I'm using Excel 2013 and I'm trying to create some drop down lists like in the following example:
https://www.youtube.com/watch?v=rLRrYPsxGa4

Here, when i'm choosing the city, let's say London, i would like to see all employees from London city, in a clear list, not in a drop down list (all 4 rows)
It should be easy, but it's not working.

From Data Validation window i did not choose the In-cell dropdown option, an nothing. If i select this value, i'll see the employees in a drop down list, but i want to see them all.

Any ideea why it's not working? How can i do those dependent lists?

Thank you

 

[FDibbins]

A dropdown list is just a means of entering data in one cell, it is used to restrict entry amd help overcome input errors, but a DD does not affect other cells (no excel formula does, they all only affect the cell they reside in)

I cannot view the video, so I have no idea what that is doing. Can you tell me what you are doing?

 

[tuspilica]

Hey FDibbins,

Things are like this:
1. i have 3 groups of fruits (orange, mango, kiwi)
2. for each type of fruit i have country producers (orange: Spain, Italy, Egypt, US; mango: India, Senegal; kiwi: Iran, Turkey, Australia, Brazille, Algeria)
3. when i choose the fruit from a drop down or a selection list i want to see the associated producers in a full clear list. If i select 2 fruits, i want to see the producers of both fruits.

One example is here: https://www.ablebits.com/office-addins-blog/2014/09/30/dependent-cascading-dropdown-lists-excel/

But, it's not working if i deselect the In-cell dropdown option.
I'm not realy sticked to select fruits from drop down list, it can be a selection from a Combo Box / List box.
In the end, if i choose orange and mango producers i want to be able to see the 2 producers in a single column, one after another without empty rows.

 

[FDibbins]

I was just looking at that link to see if there was something I missed (I may still have), but a DD only accepts a single entry, not 2 "If i select 2 fruits, i want to see the producers of both fruits."
I would suggest that to do that, you use 2 DD's

Also, as I said, a DD cannot cause other cells to be populated. You would need to use formulas on the other cells to "calc" based on what is selected in the DD.
You could do this a number of ways, 1 way would be to use a range name for each list (which you would need for the DD anyway), and then use INDEX/MATCH. Another way would be to maybe use INDEX/SMALL/IF ARRAY

For the DD, take a look at my post #6 here, and see if this will help get you started...
http://www.mrexcel.com/forum/excel-questions/922240-data-validation-list-12-sub-lists.html

 

[Jo_ox]

Wouldn't what you're trying to achieve be possible by simply creating a table inside excel with 2 columns:

Fruit	Country
Apple	1
Pinapple	2
Orange	3
Kiwi	4

<tbody>
</tbody>

And then simply sorting the columns by a specific country or for that sake a specific fruit?