Allow or block user input based on criteria

[IWebb]

datavalidationsample.ods (7.8 KB)
Hi
How do I use data validation to allow or block user input based on the cells to the left. I just want to stop data being placed in the wrong column.
I thought I could use a custom formula that evaluates a condition being true or false and if true allow user input.
In the example apples and oranges are always purchased at the market price and should only be allowed in the market column.
I thought I could use something simple like IF(B2=“Apples”) allow user input in the market column and a NOT function in the wholesale column but its always invalid.

Any help is appreciated

[mikekaganski]

@IWebb

IF(B2=“Apples”)

Use simple B2="Apples"

Generally, always try your formulas in cells before using them in validation.

[IWebb]

Thanks @mikekaganski I swear I tried this yesterday and it failed. Massive Facepalm
Also I did the formula in another column and it was fine so I wasn’t sure why it wasn’t working in data validation. It seems my error was in how I defined the name range Anyhow its working. So simple so silly

[Lupp]

Control of entries by >Data>Validity comes with disadvantages in some cases.
Control to a bit more detail and reliability you can sometimes achieve using Conditional Formatting.
The attached example shows how this can be done and gives a few explanations placed in cells.
disask85198CFusedForConditionalCellProtection.ods (30.6 KB)
Currently (my version: 7.4.3.2) the usage of Tab in the sheet with conditionally protected/unprotected cells doesn’t exactly work as expected. The wee little bug won’t do much harm.

[IWebb]

Thanks @Lupp
I had originally thought to use conditional formatting but wasn’t sure how to do it. I had forgotten I could apply a formula to conditional formatting. This is a nice tip for my future endeavors