drop-down cells-making them appear by Lookup possible?
Posted by Connie on July 30, 2001 9:01 AM
I have a drop-down cell with a list (dog,cat,bird).
I have a table "animals" which has dog in A1 and a drop-down list of dog breeds in B1 (Pug,Yorki,Collie).
Is it possible to get my drop-down list of breeds to show up if the user chooses "dog" from the original drop-down cell? I tried using Vlookup, and it just returned whatever the breed name was that had been chosen last in the table, ie Pug. It didn't paste/transfer my drop-down cell of breeds. Is this possible without VBA? Just wondering.
Connie
Posted by Aladin Akyurek on July 30, 2001 9:18 AM
Connie,
I don't believe VBA is needed here.
But, first a question: It seems you directly entered all items as source thru Data Validation Dialog, right?
Aladin
Posted by Connie on July 30, 2001 9:28 AM
Aladin,
That is correct. Data Validation was used as the method to create the list of items.
I made a mistake in my original post. The drop-down list of dog breeds within the table appears in B2, not B1 as I erroneously stated.
Posted by Aladin Akyurek on July 30, 2001 9:44 AM
>That is correct. Data Validation was used as the method to create the list of items.
>I made a mistake in my original post. The drop-down list of dog breeds within the table appears in B2, not B1 as I erroneously stated.
I can't still visualize the actual/current and desired situation. So, what follows is a guess of the desired situation.
You have a list of animals which can serve as Source to contruct a dropdown list. And, you want to "offer" yet another dropdown list of breeds that depends on what has been selected from the first one. If, e.g., dog is selected, you want the second list show items that consist of "dog breeds". If "cat"is chosen, the second list shows only "cat breeds."
Is this what wou want?
Aladin
Posted by Connie on July 30, 2001 9:49 AM
Yes, that's what I'm shooting for.
Posted by Aladin Akyurek on July 30, 2001 10:51 AM
Re: Yes, that's what I'm shooting for.
Place all the animals in a column on a separate sheet.
Select the range that contains the animals and name it ANIMALS via the Name Box.
In the column next to the ANIMALS column, place for each item in A a name. E.g., DOGS next to "do", CATS next to "cat", and so on. Select all cells in both columns and name the selected range TABLE.
Place all of the dog breeds in a different column on the same sheet and name the filled in range DOGS via the Name Box. Do the same for "cat breeds" and the respective range CATS,etc.
Activate the cell where you want your first dropdown list that will contain the items from ANIMALS. Activate Data|Validation, choose List for Allow and enter the following formula as Source.
=ANIMALS
Activate the cell where you want the 2nd dropdown list. Follow the forgoing proc. But use the follwing formula instead as Source:
=VLOOKUP(A1,TABLE,2,0)
where A1 is the cell ref in which the first dropdown list has been built.
Aladin
Posted by Aladin Akyurek on July 30, 2001 11:02 AM
Make the last one: =INDIRECT(VLOOKUP(A1,TABLE,2,0)). (NT)
Posted by Connie on July 30, 2001 11:19 AM
Error msg
Aladin,
After entering the Source for the last step (the Vlookup source), I got a msg saying, "The list source must be a delimited list, or a reference to a single row or column." I wonder why I got this msg and you didn't? Consequently, the second box wouldn't work (it wouldn't do the drop-down list). Place all the animals in a column on a separate sheet.
Posted by Connie on July 30, 2001 11:29 AM
That solved it. Thanks, Aladin! I didn't think this was possible! (nt)
