data validation & creating sub set choices based on the adjacent column choice

Hi, I am limiting choices in a certain cell to a list via data validation (using data validation, allow - list and pointing to the source). I want to limit the next set of choices in the next column (in this example in Column C) for the user based on the choice made in the adjoining cell. For example to keep it simple I will use the following: if the user selected "Fruit" in B1, then the options in C1 would only show "Oranges","Apples", "Pears". If however the user selected "Veg" it would only show "Cucumber", "Lettuce", "Spinach", "Radishes" in the drop down in column C. I can put a prefix in front of the column C data validation list if that is needed to narrow down e.g. FR-oranges, FR-apples etc.

A B C
1 Fruit Oranges
2 Veg Lettuce
3


Data Validation source for Column B (H2:H4)
Fruit
Veg
Nuts



Data Validation for Column C: (I2:I10)
Oranges
Apples
Pears
Cucumber
Lettuce
Spinach
Radishes
Almonds
Peanuts


Thanks for the input.

These are called DEPENDENT DROP DOWNs, and can be accomplished several ways. The simplest with hardcoded named ranges.

1) Highlight I2:I4 (the fruits in the list, all adjacent the way you've shown)
2) Click in the NAME BOX (to the left of the formula, it probably says "I2" currently)
3) Replace the contents of the box with the word FRUITS

You have just created a a named range called fruits that means "I2:I4"

4) Highlight I5:I7 and name those cells VEG
5) Highlight I8:I10 and name those cells NUTS

Now you can use those named ranges as the Data Validation List source.

6) Highlight C1:C100 (or whatever) and open the Data Validation window, enter these setttings:

Allow: List
Source: =INDIRECT(B1)

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.