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:

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.

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.

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.

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.