Conditional Drop Box

I am currently working on a spreadsheet and would like to make a cell that can auto-input a value (like an IF or VLOOKUP formula) but also has a conditional drop down list. Both of these would be based upon the same previous cell with its own drop down function. My main issue is that I do not need the drop down list for every condition selected.

For example: say the first cell reads "AAA". AAA would have an automatic input of "1". However, if "BBB" were selected I would need the box to create a drop down with options "2", "3", "4", and "5".

I was able to come close by utilizing an offset formula in the drop down source and an IF function in the cell. However, once a value was selected from the drop down menu it did not clear out to the original formula but rather replaced it with the value permanently. I would like for it to change with each selection while maintaining the original formula.

I realize I am probably explaining this terribly so I have attached a simple sample. Any help would be greatly appreciated!

Try look here: http://www.contextures.com/xldataval02.html

I use CONDITIONAL FORMATTING to highlight the second cell if the first cell is changed and the second cell still holds a value that is no longer a valid choice.

Here's a sample sheet I've posted showing how it's done. The sheet "CHOICES" has the dependent drop lists (and shows one of the dangers of using them) and the sheet "LISTS" shows the lists and named ranges in use.

Have a look.

Sample Files[INDENT]Dependent Drop Down Lists

--DependentLists3.xls - 3 levels


That sheet also demonstrates a VBA option that actually clears the second cell.