customizing a drop down list depending on the value of a preceding cell

[Daverich]

Hi all,

I've gotten a bit stumped on something here. and have had a look through the site on older threads but couldn't find the correct answer.

basically i have a series of columns with a yes/no drop down, however if the answer to column B is "yes" then the rest of the columns needs to read "not applicable" if the answer is "No" then they need to have the option for the Yes/No drop-down. if cant seem to get the cell to accept both, can any one help?

[dflak]

This is the first time I've used and if statement to switch ranges for data validation. =IF($B2="Yes",$R$2,$Q$2:$Q$3) I use this for validation in Columns C:E. If you answer Yes, then your only choice for the other columns is N/A. Otherwise you get Yes or No.

As for automatically filling in N/A when you answer Yes, I first thought of a formula, but that gets clobbered when you make a selection. If you want that, then it will take some VB code.