Real World Office: Changing the Choices in an In-Cell Drop Down

The Challenge
A colleague of mine created a form out of a Microsoft Excel worksheet, there are two in-cell dropdown lists (from Data Validation) – what she wants to happen is that the choices in the second data validation list changes depending on what the user selects on the first data validation list.
For example if a user chooses Fruits in the first in-cell dropdown, then the second in-cell dropdown should contain Apples, Oranges, Grapes, etc.. If the user then chooses Vegetables, then the second should contain Broccoli, Carrots, etc.

The Solution
The first thing that came into our minds is that we probably need a macro to do this (Yikes!).
Since the in-cell drop down lists are created using Data Validation and we know that the validation criteria (the contents of the in-cell drop down, in this case) can be determined by using a formula then you can use the IF function (nested in this case – depending on the number of items in the first cell) to determine which choices to display.

The logic goes something like this:

1. IF user chooses "Fruits" then user can choose between "Apples", "Oranges", and "Grapes"
2. IF user chooses "Vegetables" then the user can choose between "Carrots", "Cabbage", "Broccoli"

Step 1 – Tabulate the choices in a separate sheet in your workbook, should look something like this:


Step 2 – Go back to the second in-cell drop down, go to the Data tab in the ribbon then click data validation. Under the Validation criteria group, make sure that List is selected for the "Allow" field – then in the source field create a formula using the IF function.

The formula should look like this:

=IF([Cell Reference for the First Cell]="[Choice]",[Cell Reference for the Choices], IF([Cell Reference for the First Cell]="[Choice]",[Cell Reference for the Choices]…))

For this example the formula looks something like this:

=IF(C3="Fruits",Sheet2!$B$3:$B$5,IF(C3="Vegetables",Sheet2!$C$3:$C$5,FALSE))

Result
The choices in the second in-cell dropdown list now changes depending on the value of the first cell.
.