Limiting entries depending on entries in prior cell

Posted by Ailene Valdez on September 27, 2001 9:50 AM

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 /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. If he chooses 'veggies' from the list in cell A3, then only the list of veggies will appear in cell B3, no fruits. (this is not my real data, of course :-))

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 Travis on September 27, 2001 10:44 AM

This might not be the easiest way, but it will work. It can be done with named ranges and an if statement in your validation rule. For example, somewhere in your spreadsheet put the acceptable lists. (In the example below I put a list in C1:C3 which I named food and D1:D3 which I named supplies) THe easiest way to name a range is to highlight it and type what you want to call it in the cell reference in the upper left corner
Then put a validation rule in cell B1 (Data>>Validation). Choose list in the first dropdown and enter the following formual in the source box.
=IF($A$1="food",(food),IF($A$1="supplies",(supplies),""))

A B C D
1 food apple paper
2 banana clips
3 grape staples

Now when you key into cell B1 you will get a drop down. If A1 is "Food" the drop down will have "Apple, Banana, Grape" If A1 is supplies the drop down will be "Paper, clips, staples" Hope that helps.

Posted by Juan Pablo on September 27, 2001 11:26 AM

Instead of doing that formula in B1, use

=INDIRECT($A$1)

That'll do the same thing.

Juan Pablo

---------------

Posted by Travis on September 27, 2001 12:32 PM

A B C D

my example didnt come out very well, so here it is again. here is a link to the actual file if you want to download it.
http:\\www.cableone.net\tharr\example2.exe
Its a self extracting zip file, select run from current location then unzip.
If you accept the default it will now be located at C:\Example2.xls

Posted by Travis on September 27, 2001 8:08 PM

Final thought, with NO NAMED RANGES

OK...my final thought
using your example, make your custom validation like this
Its a lengthy formula, but no named ranges.

=IF(AND(A1="fruit",OR(B1="apple",B1="banana",B1="grape")),TRUE,(IF(AND(A1="vegitable",OR(B1="carrot",B1="lettuce")),TRUE,(IF(OR(EXACT(UPPER(A1),"VEGITABLE"),EXACT(UPPER(A1),"FRUIT")),FALSE,TRUE)))))

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