How to Change List Based on Value Chosen in Another List

E

Edwin Kelly

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.
 
M

Miguel Zapico

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.
 
E

Edwin Kelly

Thanks so much. Your suggestion was the same as the one Ron's link directed
me to. It works !
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word VBA - Automatic change content of a cell in table using dropdown list 5
How to change margins around bullets in list 1
List from a Table 1
Drop down list control value in vba 2
Validation of cell based on Drop down list 1
Formula for looking up and copying from another worksheet? 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Calculate the median value before a specified list 0

Top