How to Change List Based on Value Chosen in Another List
Is there a way to have 2 columns with dropdown List Validation where if you
choose an item in List A it will automatically populate List B with the available choices? For example, Column A contains a list of Car Makers referred to by a named range somewhere else in the sheet or workbook. Honda is chosen in Column A so Column B should have the list of Honda vehicles to choose from. I had assumed arranging the range data so that in one column all the car makers are listed and to the right of each car maker the models would be listed in the cell to the right, however many it took for each. I want to limit the dropdown to show only the valid choices. |
How to Change List Based on Value Chosen in Another List
Check Debra Dalgleish's website for Dependent List instructions:
http://www.contextures.com/xlDataVal02.html Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Edwin Kelly" wrote: Is there a way to have 2 columns with dropdown List Validation where if you choose an item in List A it will automatically populate List B with the available choices? For example, Column A contains a list of Car Makers referred to by a named range somewhere else in the sheet or workbook. Honda is chosen in Column A so Column B should have the list of Honda vehicles to choose from. I had assumed arranging the range data so that in one column all the car makers are listed and to the right of each car maker the models would be listed in the cell to the right, however many it took for each. I want to limit the dropdown to show only the valid choices. |
How to Change List Based on Value Chosen in Another List
It can be done using indirect and vlookup in the following way:
1.- Create a list with all the items in list B, sorted in a way that all different categories in list A are consecutives. Note the ranges for all the options on list A (v.gr. C2:C7 for option 1, C8:C15 for option 2 and so on) 2.- Create a two columns list, with all the items on list A in the first column, and the corresponding ranges on the second column 3.- Create the first drop-down list validation, based on the items on list A 4.- In any place of the sheet, use the vlookup formula to look for the range associated with the list A drop-down list against the list from step 2. This will dinamically change with every selection on list A. 5.- In the list B validation, use the formula "=indirect(Whatever cell you used on step 4)" The drop-down list will use now the values on the range explicited on that cell. -- It is nice to be important, but it is more important to be nice "Edwin Kelly" wrote: Is there a way to have 2 columns with dropdown List Validation where if you choose an item in List A it will automatically populate List B with the available choices? For example, Column A contains a list of Car Makers referred to by a named range somewhere else in the sheet or workbook. Honda is chosen in Column A so Column B should have the list of Honda vehicles to choose from. I had assumed arranging the range data so that in one column all the car makers are listed and to the right of each car maker the models would be listed in the cell to the right, however many it took for each. I want to limit the dropdown to show only the valid choices. |
How to Change List Based on Value Chosen in Another List
Thanks so much. Your suggestion was the same as the one Ron's link directed
me to. It works ! "Miguel Zapico" wrote: It can be done using indirect and vlookup in the following way: 1.- Create a list with all the items in list B, sorted in a way that all different categories in list A are consecutives. Note the ranges for all the options on list A (v.gr. C2:C7 for option 1, C8:C15 for option 2 and so on) 2.- Create a two columns list, with all the items on list A in the first column, and the corresponding ranges on the second column 3.- Create the first drop-down list validation, based on the items on list A 4.- In any place of the sheet, use the vlookup formula to look for the range associated with the list A drop-down list against the list from step 2. This will dinamically change with every selection on list A. 5.- In the list B validation, use the formula "=indirect(Whatever cell you used on step 4)" The drop-down list will use now the values on the range explicited on that cell. -- It is nice to be important, but it is more important to be nice "Edwin Kelly" wrote: Is there a way to have 2 columns with dropdown List Validation where if you choose an item in List A it will automatically populate List B with the available choices? For example, Column A contains a list of Car Makers referred to by a named range somewhere else in the sheet or workbook. Honda is chosen in Column A so Column B should have the list of Honda vehicles to choose from. I had assumed arranging the range data so that in one column all the car makers are listed and to the right of each car maker the models would be listed in the cell to the right, however many it took for each. I want to limit the dropdown to show only the valid choices. |
How to Change List Based on Value Chosen in Another List
Thanks so much that did it.
"Ron Coderre" wrote: Check Debra Dalgleish's website for Dependent List instructions: http://www.contextures.com/xlDataVal02.html Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Edwin Kelly" wrote: Is there a way to have 2 columns with dropdown List Validation where if you choose an item in List A it will automatically populate List B with the available choices? For example, Column A contains a list of Car Makers referred to by a named range somewhere else in the sheet or workbook. Honda is chosen in Column A so Column B should have the list of Honda vehicles to choose from. I had assumed arranging the range data so that in one column all the car makers are listed and to the right of each car maker the models would be listed in the cell to the right, however many it took for each. I want to limit the dropdown to show only the valid choices. |
All times are GMT +1. The time now is 02:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com