can anyone help....

I want to use data validation to allow a drop down list in a cell but only
if certain criteria are met.

e.g. If A1="yes" then show yes in the validation cell, if b1="no" then show
no, if not then shown the dropdown list for yes/no

At the moment i have the validation in cell d1 as a list with this formula:
=IF(OR(A1="",B1=""),YESNO,IF(A1="YES","YES",IF(B1= "NO","NO",YESNO))))

With the name of my list being yesno

thanks for any help

This formula, with a Data Validation type of List is not quite what you
asked, but will only allow Yes if A1 is Yes, No if B1 is No, or else gives a
list of what is in G1:G2

=IF(A1="Yes","Yes",IF(B1="No","No",G1:G2))

pre-load G1:G2 with Yes, No

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"franko" wrote in message
...
can anyone help....

I want to use data validation to allow a drop down list in a cell but only
if certain criteria are met.

e.g. If A1="yes" then show yes in the validation cell, if b1="no" then
show
no, if not then shown the dropdown list for yes/no

At the moment i have the validation in cell d1 as a list with this
formula:
=IF(OR(A1="",B1=""),YESNO,IF(A1="YES","YES",IF(B1= "NO","NO",YESNO))))

With the name of my list being yesno

thanks for any help

In your YESNO range, select the Yes cell, and name it YES, and select
the No cell, and name it NO.

In the Data Validation formula, refer to these ranges:

=IF(A1="Yes",YES,IF(B1="No",NO,YESNO))

franko wrote:
can anyone help....

I want to use data validation to allow a drop down list in a cell but only
if certain criteria are met.

e.g. If A1="yes" then show yes in the validation cell, if b1="no" then show
no, if not then shown the dropdown list for yes/no

At the moment i have the validation in cell d1 as a list with this formula:
=IF(OR(A1="",B1=""),YESNO,IF(A1="YES","YES",IF(B1= "NO","NO",YESNO))))

With the name of my list being yesno

thanks for any help


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Looks like both you and Bob are saying the same thing.

This solution does work because it will not allow users to enter an invalid
answer. But i am guessing that there is no way of having the "yes" to fill
in all the other cells if my criteria are met??

If not.... no worries and thanks to you both.


"Debra Dalgleish" wrote:

In your YESNO range, select the Yes cell, and name it YES, and select
the No cell, and name it NO.

In the Data Validation formula, refer to these ranges:

=IF(A1="Yes",YES,IF(B1="No",NO,YESNO))

franko wrote:
can anyone help....

I want to use data validation to allow a drop down list in a cell but only
if certain criteria are met.

e.g. If A1="yes" then show yes in the validation cell, if b1="no" then show
no, if not then shown the dropdown list for yes/no

At the moment i have the validation in cell d1 as a list with this formula:
=IF(OR(A1="",B1=""),YESNO,IF(A1="YES","YES",IF(B1= "NO","NO",YESNO))))

With the name of my list being yesno

thanks for any help


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You could also add a formula to the cell:

=IF(A1="Yes","Yes",IF(B1="No","No","--Select--"))

but if users select from the dropdown list their selection will replace
the formula.

franko wrote:
Looks like both you and Bob are saying the same thing.

This solution does work because it will not allow users to enter an invalid
answer. But i am guessing that there is no way of having the "yes" to fill
in all the other cells if my criteria are met??

If not.... no worries and thanks to you both.


"Debra Dalgleish" wrote:


In your YESNO range, select the Yes cell, and name it YES, and select
the No cell, and name it NO.

In the Data Validation formula, refer to these ranges:

=IF(A1="Yes",YES,IF(B1="No",NO,YESNO))

franko wrote:

can anyone help....

I want to use data validation to allow a drop down list in a cell but only
if certain criteria are met.

e.g. If A1="yes" then show yes in the validation cell, if b1="no" then show
no, if not then shown the dropdown list for yes/no

At the moment i have the validation in cell d1 as a list with this formula:
=IF(OR(A1="",B1=""),YESNO,IF(A1="YES","YES",IF( B1="NO","NO",YESNO))))

With the name of my list being yesno

thanks for any help


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

This should work as i will be able to make the file read only as it is going
to be an automatic quotation system.

Thanks for all your help.

Frank


"Debra Dalgleish" wrote:

You could also add a formula to the cell:

=IF(A1="Yes","Yes",IF(B1="No","No","--Select--"))

but if users select from the dropdown list their selection will replace
the formula.

franko wrote:
Looks like both you and Bob are saying the same thing.

This solution does work because it will not allow users to enter an invalid
answer. But i am guessing that there is no way of having the "yes" to fill
in all the other cells if my criteria are met??

If not.... no worries and thanks to you both.


"Debra Dalgleish" wrote:


In your YESNO range, select the Yes cell, and name it YES, and select
the No cell, and name it NO.

In the Data Validation formula, refer to these ranges:

=IF(A1="Yes",YES,IF(B1="No",NO,YESNO))

franko wrote:

can anyone help....

I want to use data validation to allow a drop down list in a cell but only
if certain criteria are met.

e.g. If A1="yes" then show yes in the validation cell, if b1="no" then show
no, if not then shown the dropdown list for yes/no

At the moment i have the validation in cell d1 as a list with this formula:
=IF(OR(A1="",B1=""),YESNO,IF(A1="YES","YES",IF( B1="NO","NO",YESNO))))

With the name of my list being yesno

thanks for any help


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html