Data Validation with formulas

[Peak Seagull]

Hi,

Hoping someone can help.

In the attached spreadsheet I would like to always return “No” in column C if the answer in column B is also “No”.

As data validation has been applied to column C it doesn’t seem to like applying a formula to achieve the above result.

I can’t remove the data validation because if someone chooses “Yes” in column B then they still have the option to choose “Yes” or “No” in column C.

Hope this makes sense!!

Thank you for your help.

 

[navic]

See if it helps you, an idea with a named range and IF function in Data Validation.

=IF(B5="yes",yes,no)

 

[Peak Seagull]

See if it helps you, an idea with a named range and IF function in Data Validation.

=IF(B5="yes",yes,no)

Hi Navic - that's a very clever way of getting around it. Is there another way of automatically returning “No” in column C if the answer in column B is also “No” without having to choose “No” from the drop down list even though it is the only choice?

 

[Peter Bartholomew]

@navic I have played further with your validation to make it two-way. That is 'implemented?=Yes' prevents the selection of 'No' for applicable.
The 'shadow' 'Yes' and 'No' are generated by a formula in the preceding cell.

 

[Peak Seagull]

@navic I have played further with your validation to make it two-way. That is 'implemented?=Yes' prevents the selection of 'No' for applicable.
The 'shadow' 'Yes' and 'No' are generated by a formula in the preceding cell.

Thanks ever so much navic that's great.

 

[navic]

Thanks ever so much navic that's great.

That should be addressed to Peter

 

[Peak Seagull]

@navic I have played further with your validation to make it two-way. That is 'implemented?=Yes' prevents the selection of 'No' for applicable.
The 'shadow' 'Yes' and 'No' are generated by a formula in the preceding cell.

Thanks ever so much Peter that's great.

 

[Peter Bartholomew]

I reposted the workbook because I was not sure the '@' operator would work correctly converted to older versions of Excel.
The partially-hidden formulas would need locking and protecting, sooner or later someone will delete them otherwise.
The 'Yes∕No' name uses a 'Division Slash' because the normal solidus '/' is an illegal character in a defined name.
I am perfectly happy to have your thanks directed to @navic, the bulk of the solution was his .