Choose from list depending on entry in the prior cell

Posted by Ailene on September 26, 2001 10:38 PM

I have a data validation list in column A. How do I limit the list/choices for column B that depends on the user's entry in column A?

For example,
FOOD CATEGORY KIND
fruit banana
fruit apple
vegetable carrot
fruit grapes
vegetable lettuce

If user chooses 'fruits' from list in cell A2, then the only choices he has available for cell B2 are kinds of fruits, no vegetables appear on the list.

Am new to this board, but this site is now bookmarked! I've been using spreadsheets for a while, but haven't really used macros or any VBA coding, so please let me know if my dilemma can be solved just with the knowledge I have now, or will it require more studying on my part?

Thanks. Ailene

Posted by Aladin Akyurek on September 27, 2001 10:12 PM

Ailene --

Take a new worksheet and name it Tables.

In A1 enter: Main List

In A3 enter: Fruits
In A4 enter: Vegetables

Select A3:A4, activate the Name Box in the Formula Bar, and type MList in that box.

In B1 enter: Fruits

In B3 enter: Banana
In B4 enter: Apple
In B5 enter: Grapes

Select B3:B5, activate the Name Box, and type Fruits.

In C1 enter: Vegetables

In C3 enter: Carrot
In C4 enter: Lettuce

Select C3:C4, activate the Name Box, and type Vegetables.

Now go to the sheet where you want to apply/use data validation.

Activate A2, activate Data|Validation, choose List for Allow, and enter as source:

=MList

Click OK.

Activate B2, activate Data|Validation, choose List for Allow, and enter as source:

=INDIRECT(A2)

Select A2:B2 and copy down as far as needed.

Aladin