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