dynamic dropdown with named range

[winti92]

Hello

Please consider the table below. I try to create a dynamic dropdown list. My first dropdown list consists of all the Names in Column 1. The second dropdown list should then show the favorite Fruits 1, 2 and 3 of the chosen person. I used a named range for the first dropdown Table1[Name]) but don't know how to do the second one.

Can someone help me on this?

Thanks in advance!

Name	Fruit 1	Fruit 2	Fruit 3
Alex	Apple	Banana	Orange
Peter	Orange	Apple	Lemon
James	Apple	Apricot	Mandarin

<TBODY>
</TBODY>

 

[Fishboy]

Hi Winti92,

The easiest way to achieve this is to create a small table on a second sheet (which can be hidden afterwards if you prefer). The small table basically outlines what options are available for each of the choices from the first drop-down box. See my example below:

Excel 2010
	A	B	C	D
1	Name	Alex	Peter	James
2	Alex	Apple	Orange	Apple
3	Peter	Banana	Apple	Apricot
4	James	Orange	Lemon	Mandarin
Sheet1

Once you have created your grid as above you need to define some named ranges.


Select cells A2:A4, then in the range box to the left of the formula bar type in Name
Next select cells B2:B4 and in the range box type in Alex and press enter.
Now select C2:C4 and in the range box type in Peter and press enter.
Finally select D2:D4 and in the range box type James and press enter.


Now go back to your main sheet where you want the drop-down boxes. Select where you want the first drop-down box to go (for examples sake we will say in B2), select Data Tab->Data Validation->Data Validation. Select the "List" option from the drop-down menu and then in the "Source" box type in =Name. You'll see the first drop down box on your sheet now contains the list from cells A2:A4 from the hidden sheet.


Next, move along to where you want the next drop-down box to be and select Data Tab->Data Validation->Data Validation. Select the "List" option again, but this time in the "Source" box type in =INDIRECT($B2). This will probably give a warning that the result is currently an error, but that is fine. The result it this should now look at whatever value is given in the first drop-down box in B2 and uses the answer to display the relevant list of fruits in order for the selected name.

 

[Osvaldo Palmeiro]

You can assign names to groups of fruit and use these names in the INDIRECT function.
Take a look at the link below.

http://www.contextures.com/xlDataVal02.html

 

[winti92]

Hi Fishboy

Thanks for your answer. I know this option, but unfortunately my table has 100-200 rows and 7 columns. So first of all, chanching the format of the table would make it very unclear and second, I don't have the time to define 100-200 names. Especially since the entries in the table can change overtime, the dropdowns should remain flexible. Therefore I just defined the first name as all entries in column 1 of the table and want now, that in the second dropdown one can choose between the entries of column 2-7 of the respective choice from the first dropdown.

 

[Fishboy]

Hi Fishboy

Thanks for your answer. I know this option, but unfortunately my table has 100-200 rows and 7 columns. So first of all, chanching the format of the table would make it very unclear and second, I don't have the time to define 100-200 names. Especially since the entries in the table can change overtime, the dropdowns should remain flexible. Therefore I just defined the first name as all entries in column 1 of the table and want now, that in the second dropdown one can choose between the entries of column 2-7 of the respective choice from the first dropdown.

In order for the name in the first drop-down box to have any relevance on the contents of next drop-down box, there needs to be a list called that name. If you have 100-200 different names that would imply 100-200 different lists, one for each name.

If you don't want to have to create that many named ranges / lists, dependent drop-down boxes might not be the way to go. You would be better off using a vlookup which would take the selected name from the drop-down box then lookup in your table to find the corresponding fruit choices of that person.

Excel 2010

	A	B	C	D
1	Name	Fruit 1	Fruit 2	Fruit 3
2	Alex	Apple	Banana	Orange
3	Peter	Orange	Apple	Lemon
4	James	Apple	Apricot	Mandarin
5
6	Data Validated List - Name	=VLOOKUP(A6,A2:D4,2,FALSE)	=VLOOKUP(A6,A2:D4,3,FALSE)	=VLOOKUP(A6,A2:D4,4,FALSE)

<tbody>
</tbody>

Sheet1​

Using your example data again, A6 is where the drop-down list for NAME is.
In B6 I put =VLOOKUP(A6,A2:D4,2,FALSE)
In C6 I put =VLOOKUP(A6,A2:D4,3,FALSE)
In D6 I put =VLOOKUP(A6,A2:D4,4,FALSE)

Now, when you change the selected name from the drop-down list, the 3 favourite fruits update accordingly.

 

[winti92]

In order for the name in the first drop-down box to have any relevance on the contents of next drop-down box, there needs to be a list called that name. If you have 100-200 different names that would imply 100-200 different lists, one for each name.

If you don't want to have to create that many named ranges / lists, dependent drop-down boxes might not be the way to go. You would be better off using a vlookup which would take the selected name from the drop-down box then lookup in your table to find the corresponding fruit choices of that person.

Excel 2010

	A	B	C	D
1	Name	Fruit 1	Fruit 2	Fruit 3
2	Alex	Apple	Banana	Orange
3	Peter	Orange	Apple	Lemon
4	James	Apple	Apricot	Mandarin
5
6	Data Validated List - Name	=VLOOKUP(A6,A2:D4,2,FALSE)	=VLOOKUP(A6,A2:D4,3,FALSE)	=VLOOKUP(A6,A2:D4,4,FALSE)

<TBODY>
</TBODY>

Sheet1​

Using your example data again, A6 is where the drop-down list for NAME is.
In B6 I put =VLOOKUP(A6,A2:D4,2,FALSE)
In C6 I put =VLOOKUP(A6,A2:D4,3,FALSE)
In D6 I put =VLOOKUP(A6,A2:D4,4,FALSE)

Now, when you change the selected name from the drop-down list, the 3 favourite fruits update accordingly.

thanks for your help. but unfortunately that is not an option for me, since I need to be able in the tool to choose a fruit. And dependent on the fruit choice other cells will be filled in. So I really need to choose a name and then the fruit.

 

[Fishboy]

So I really need to choose a name and then the fruit.

Sounds like you have a whole load of named ranges / list to create!

 

[winti92]

isn't there a possibility to insert a function in data validation so that it looks up the value from B2 in a specific table and outputs then the 8 options on the right side of the elected value in this table?

 

[mick0005]

isn't there a possibility to insert a function in data validation so that it looks up the value from B2 in a specific table and outputs then the 8 options on the right side of the elected value in this table?

Winti,

Here is a link to a file I put together for another member that relies on a solution I often use for this purpose. This concept came from the brain of Jerry Beaucaire and I have just customized it a bit for my normal application.

You can either enter your options for selections on Sheet 1, which will then automatically be entered into the setup required for the data validation to work (On DV Lists sheet), or you can enter them directly on the DV Lists sheet. Just clear out the formulas from that sheet if that is the route you choose.

https://www.dropbox.com/s/inex3shhqix6t89/Cascading DV_Ajnaaslam.xlsm?dl=0