Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

enter image description here

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 :)

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.