Conditional Data Validation

Question

I've been taking a certification course on Excel. It's a beginner class but something caught my attention. Specifically, Data validation.

We learned how to do it using the default conditions but I would like to include my own using formulas.

Here is where I'm at:

Now, what kind of formula would I put here? Would I use an if? If so, what would I return? Would I return TRUE or FALSE?

I think the best way for me to learn would to be if somebody showed me an explicit example of one. So, for that purpose, here is a situation:

If B2 is exactly 28000, throw the validation error without using the default validation criteria. Thanks guys :)

Answer 1

You must enter a formula that returns true for valid and false for invalid. Note that even if you are checking the content of the selected cell itself you still need to explicitly reference it. So in your example the formula would be

=B2 <> 28000

The above works because the <> operator compares the value of B2 to 28000 and returns true if they are not equal, false if they are equal.

I recommend the following online help page which covers custom validation in more detail, including more complex examples: http://office.microsoft.com/client/helppreview14.aspx?AssetId=HP010342173&lcid=2057&NS=EXCEL&Version=14&tl=2&queryid=&respos=1&HelpID=21314