Forgot to mention that this formula is to be used in 'Source' field of Data
Validation.

For example if you want the drop down in cell B1 based on cell A1; select
cell B1 and from menu DataValidationAllow 'List'

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

If you are looking for a non-VBA solution create two named ranges myListYes
and myListNo and use the below formula..This will not autopopulate the value
'n/a'.

=IF(A1="Yes",mylistYes,IF(A1="No",myListNo,""))

If you really need a VBA solution post back..


--
Jacob (MVP - Excel)


"Mezani" wrote:

I have two cells that each have drop down lists. The first cell has the text
yes and no, If i choose yes i want the second cell to return N/A for example,
and if it is no I want the drop down list to be usable in the second cell.

Thanks for any help