0

Using Excel 2013, I need to create a data validation rule without using VB or other code. Cell D6 is a string that contains either "women" or "men". Cell E6 contains a value.

If D6 contains "women", the value in E6 s/b <10000. If D6 contains "men", E6 s/b <6000. Values greater than these numbers should result in a data validation error.

The formula below works for either women or men but I can't get it to work for BOTH. How do I validate for both strings in a single validation check? Thank you!

=IF(ISERROR(FIND("Women",$D6)),$E6,10000)

Improve this question

1 Answer 1

Reset to default
1

You are only looking at 1 case.

=OR(AND(D6="Women",E6<10000),AND(D6="Men",E6<6000))

This looks at either case being true. I did not use the find method as the string "Men" forms part of "Women" and I was not sure if it was case sensitive. Now I was also assuming that Men or Women was the only part of the string. If your string was longer such as "Women with purple hair" then the above formula would need adjusting.

To get to Excel's build in Data Validation in Excel 2013 select the Data Ribbon (red circle) and then select Data Validation in the ribbon (blue circle) as shown below:

How to get to data validation

In the Data Validation dialogue box, select custom from the Allow: drop down box, and paste the above formula in the formula box.

Data Validation with custom formula

If you want to customize your error messages, select the Input Message tab;

Input Tab

or the Error Alert tab:

Error Tab

Now there are some limitations to watch out for. When you enter the number into E6 it will perform the data validation check. If after you enter the number in E6 you Change D6 to the other gender, it will not recheck E6 to see if its a valid entry.

For example, if you have Women as your D6 value, you can then enter 9000 as a value in E6. If you then change the value of D6 to Men, you would then See a value of D6= Men and E6=9000 because the value of E6 has not been revalidated.

UPDATE

This is an image showing the results of the formula. The formula is in column B and the test values are in column D and E.

Example of results

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you! Women and men are not case-sensitive, and they do only form part of the string. If I enter a number >10000 in E6 for women, or >6000 for men, I want a data validation error message. The solution you provided almost gets me there, but I'm still not getting a validation error message. What further suggestions do you have? Thank you again!
I did not realize you were looking for an error message. I thought you were simply looking for a formula that would evaluate to true or false. My apologies as I tend to colour code cells for error validation. Normally I would but the above formula for both cells E6 and D6 and then change the cell background colour to red, text to yellow and bold so it would pop visually that something is wrong. Give me a moment while I poke around with error message potential
Sorry about my earlier comment about colouring the cells, I was mixing data validation up with conditional formatting.
You provided a formula that gives a 'False' error message, and remains so regardless of the value in E6. I need a data validation box to pop-up if the values in E6 are exceeded (I don't want the 'False' message.) I can figure out the pop-up; it's the formula I need help with. Can you further refine it works if the value limits are exceeded? You've been a great help so far...Thank you!
When I enter Men for D6 and 5000 for E6, the formula equates to true. When I Enter Women in D6 and 9000 in E6, the formula equates to true. I even tested with upper and lower case for the first letter and that does not affect the results. When I enter Men in D6 and 9000 in E6, the formula equates to false
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.