List Only

How to create a dropdown list in Microsoft excel such that corresponding information will also appear?

Hi! I'm creating an excel database right now. I know how to create a dropdown list. But what if for example given this data: ProductID ProductName 1 shampoo 2 soap 3 toothpaste What i want is that to create a dropdown list for ProductID and ProductName, so that if i select 1 in the Dropdown list for the column ProductID, the corresponding dropdown list for ProductName will only show shampoo. How will i do it? Thanks!

Public Comments

1. Put your list in some cells off to the side (somewhere you're not going to use, either way down or way to the side (e.g. AA1-AA5). Populate cell AA1 with '1-Shampoo', AA2 with '2-Soap', AA3 with '3-Toothpaste' and so on. Select the cell where you wish the dropdown box to be located. Click on Data>Validation from the Menu Bar. On the Settings tab select 'List' from the Allow dropdown box. Then in the Source textbox refer to your cell list (e.g. =$AA$1:$AA$3). Click on the icon on the right edge of the textbox to allow you to point & select your list saving you the time of having to type in the source.
2. If I understand you correctly, you want a second dropdown list that will change depending on what is in the first drowpdown list. If so, it takes some setting up, but here's how to do it: First set up a table with your ProductID along the top, with a one-word (nospaces) version of ProductID in the next row, and all the Product Names in the rows below. C1:E1: Product IDs for the menu (i.e "toothpaste", "bath towel") C2:E2: One-word versions of each product (no spaces - i.e. "toothpaste", "bathtowel") C3:C8: ProductName options (i.e. "Yummy Paste", "Tingle Teeth") D3:D8: ProductName options (i.e. "Fluffy Towel", "A1 Towel") E3:E8: you get the picture Next, name each of the ProductName ranges using EXACTLY the same name that you gave in C2:E2. In the tool-bar above, theres a white area which tells you the name of the cell or cells you are on. For example, select area D3:D8 and enter "bathtowel" in this box. (You can also create or modify named ranges by going to: Insert>Name>Define.) Now, create the first pull-down menu: A2: go to data validation>list>source>input =C1:E1 Next, put this formula anywhere you want to hide it (say in Z4): Z4: =HLOOKUP(A2, C1:E2, 2, false) This will be referred to by the second menu to find your ProductNames range. Create your second pull-down menu: B2: go to: data validation>list>source> input =INDIRECT(Z4)
3. If there is a one to one relationship. ID 1 = Shampoo. Then only 1 drop down list is need. Its just a matter of using vlookup to find that 1 = shampoo. But on the other hand, if it is a one to many relationship. ID1 = shampoo Normal, shampoo dry hair, shampoo mint etc.. then it becomes trickier. If you just want to get the job done quick, all you need to learn is how to filter the list.