Dependent drop-down lists

[charlesbaxter]

Hi everyone,


I am trying to sort out some pre-populated drop down menus.


Depending on which one of 3 is selected in the first drop down, other options will be pre-populated in the next cell ie.


Food Meat Vegetable Fish


Meat Beef Carrot Cod

Vegetable Chicken Peas Haddock

Fish Pork Cabbage Tuna


In this case - if meat is selected, in A1, only Beef,Chicken and Pork will appear in B1, and the same for vegetable and fish.


Thanks


Charles

 

[fred]

check out this link


http://www.youtube.com/watch?v=e-cRsGYDj-I&feature=plcp&context=C39e7847UDOEgsToPDskJ3c92uJNn6yELw_nWGK225

 

[charlesbaxter]

Thanks, that helps but I cant seem to complete the Indirect part, and now I can't delete lists I do not want. Any ideas?

 

[fred]

the indirect part should be


=indirect(cell location of the first pull down menu that shows the choices of Meat vege or fish&"List")


That said, you need to build your 3 lists as "MeatList", "VegetableList" and "FishList" because the spelling has to be the same as in cell A1 (showing up as Meat, Vegetable or Fish).


in this example it would be =indirect(A1&"List")

 

[NARAYANK991]

Hi ,


Another way of creating dependent drop down lists has already been explained in this same website ; I'll post the link here , but simply explained , it lies in creating several columns which have the data for the dependent drop downs as follows :


1. Suppose your first drop down consists of three items "Meat" , "Vegetable" and "Fish".

2. Let us assume this data is in the cells B7 through B9 ; I have deliberately chosen such unlikely addresses so that there is no chance of any misunderstanding.

3. Now , assume that each of the items in (1) above , has the following dependent choices : Meat will offer "Beef" , "Chicken" and "Pork" , Vegetable will offer "Carrot" , "Peas" and "Cabbage" , Fish will offer "Cod" , "Haddock" and "Tuna".

4. In a separate column , say column M , starting from cell M10 , have the following list :


Meat , Meat , Meat , Vegetable , Vegetable , Vegetable , Fish , Fish , Fish


The above data will occupy cells M10 through M18.


5. In column N , starting from cell N10 , have the data :


Beef Chicken Pork Carrot Peas Cabbage Cod Haddock Tuna


6. Assume your two drop downs are in cell A2 and B2 ; for A2 , select List , and give the source as =$B$7:$B$9


7. For B2 , select List , and give the source as :


=OFFSET($N$10:$N$18,MATCH(A2,$M$10:$M$18,0)-1,0,COUNTIF($M$10:$M$18,A2))


Narayan


The link is : http://chandoo.org/wp/2008/11/25/advanced-data-validation-techniques-in-excel-spreadcheats/

 

[Istiyak]

Hi Charles.,,,,


I have uploaded 1 file for ur solution on my website. Hope it will help ful to u>>??


Path : http://istiyakshaikh.hpage.com > Downloads > Dependend Para List


Enjoy... if any query plz let me know.


Regards

!$T!