Dropdown list with a condition

[bunt.]

I've prepared a calculator that asks users a series of questions; some of
them being 'yes' or 'no' type. The answers for the y/n type are listed in a
drop down menu validated from two hidden cells. My problem is that only one
of those answers should be allowed as a 'Y' at any one time. Therefore, I
need to find a way to force the other list to either change to a 'No' or at
least prompt the user.

I've tried an if statement like this in attempt to force the cell to change
but it doesn't work.
=IF(AND(G30="Y",G31="Y"), G31="N")

Any help would be appreciated.

 

[Gary Brown]

Assume:
The 2 hidden cells are Z1 and Z2.
Put 'N' in cell Z1.
Put the following formula in cell Z2.
=if(or(G30="Y",G31="Y"),"","Y")

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select
''''''''''''''''YES'''''''''''''''' at the bottom of the post.

 

[bunt.]

Thank you very much, this was exactly what I was looking for!