Thread: How do I use one list drop down menu result to select another list?

Hi all:

I've got a situation that I hope someone can help me out of.

I have set up a worksheet containing various keywords that I want to use to further define the keywords as to category and then subcategory.

I have placed the category and subcategory data in the same workbook, albeit on separate worksheets.

I have been successful in generating lists for the category data and the subcategory data.

I have also been successful in creating a drop down menu for the 19 different categories, using MS's online help site, and placed that into a column using the Validation>settings function.

This is far as I have gotten, now the difficult bit.

What I want is probably easiest to describe with an example:

In the Column CATEGORY, each cell is set so that when I click on the icon to access the menu of categories, I can then select one.

Now, if I select, say, SPORTS, as the category, then in the cell adjacent to the right in column SUBCATEGORY is then loaded with a drop menu of the subcategories in Sports, e.g., football, basketball, swimming, etc.

I'm guessing it wants to be some sort of if>then statement:

if(value of cell B2="SPORTS") then (C2=list of sports in a drop menu)

Can someone help?

All the best,

Steve

Thanks Noob.

I did that, and downloaded the sample sheet. But in that sheet, once I select a Category, and then select a subcategory, if I go back and try to reselect Category again, I get a "N/A" result.

In other words, it doesn't seem like I can change my mind once I make the first Category selection?

Noob:

If you go to the same URL as you directed me to, and download the sample "DataValDepend.xls" and test the first sheet. Select Category, then select Item, e.g., fruit>banana.

Once this is done, and change your mind, the only available option in Category is "N/A".

Fair enough, I've experimented, and if you clear the "Item" cell, you can once again select a Category.

On the named ranges sheet, H2 is "N/A", so I guess the creator of this file did this for a reason.

Still it would be nice to be able to reselect a Category and have it change the Item range.

But, hey, the solution offered is a heck of a lot better than where I was.

Steve

NOOB:

THANKS MUCH! Sorted.

I just left the N/A deali-o out of the mix and it works perfectly!!

Steve