Make cell = 0 if another cell = "Yes", but allow any value when it ="No" (see post sorry)

[shelbycharged]

Sorry for a beginner level Question,

I have a cell that I want it to only be allowed to =0 when Cell B4="Yes" (it has a drop down yes or no). When B4="No" I want the user to be able to input any numerical value into C4.

I tried using Data Validation, by putting custom: =IF(B4="Yes",0) but then it errors when I try to input a value in C4 when B4="No"

Any ideas? Thanks for any help

[evertjvr]

Which cell is the one that can only be 0 if yes? C4?
What is in the other cells around B4 and C4?

[Pauleyb]

Also, what do you want to happen if B4="No" and C4=5, then you change B4 to "Yes"? Do you want C4 to automatically change to 0? If yes, then I think you are asking for some VBA which monitors your input. If not, then it may be a bit easier.

Pauley

[shelbycharged]

Which cell is the one that can only be 0 if yes? C4?
What is in the other cells around B4 and C4?

I would like c4 to be zero if B4="Yes"
s.JPG

Also, what do you want to happen if B4="No" and C4=5, then you change B4 to "Yes"? Do you want C4 to automatically change to 0? If yes, then I think you are asking for some VBA which monitors your input. If not, then it may be a bit easier.

If B4="no" I want to allow the user to input any value into the cell. If they make B4="Yes" afterwards, I would atleast need an error to show up. I just need some way of informaing the user that the data will be incorrect.
I would love to learn how to do VBA code, do you know of any free e-books?

[Pauleyb]

Two solutions attached.
The first uses custom data validation. Pros include it forces proper input and uses error dialog boxes. Con is that you can no longer choose "Yes" or "No" from a list.

The second uses conditional formatting to highlight invalid data. Opposite Pros/Cons as listed above.

Plenty of VBA examples around the web. Still would recommend a good book to learn, but that is personal preference.

Pauley