VLOOKUP and Data Validation in same cell

[fosterlib1]

Hello!

I have a data validation list in cell A2 with four options. They correspond with one of four responses and are presented with a VLOOKUP function in cell B2. Although choosing one of the four options in cell A2 provide a "preferred" VLOOKUP response in cell B2, this response is not mandatory. I would then also like to allow the end user to choose one of the other three responses if they choose in cell B2. The problem I am running into is that I can't have both a VLOOKUP function and data validation list in cell B2.

Does anyone have a nice work around for this?

I would be comfortable with both a VBA or non VBA solution.

Thank you in advance.

 

[svendiamond]

You're not going to able to leave a formula in-tact in a cell and write over it with another choice. I would suggest simply using C2 as a "final decision" of sorts.

 

[mick0005]

Do you basically want the drop down options in cell B2 to be predicated upon the selection in A2? If so, this can be done using some named ranges for the data validation.

A few questions to determine how hairy this would need to get:

1. How many options are there in the Data Validation for A2?
2. Will the drop down options in A2 need to grow (add more options at some point in the future)
3. Will the number of options for each A2 selection (the options in B2 that are predicated on A2) need to grow/change, or are these static?