CODEWITHSUNDEEP
excelvalidation
eticket
eticket

Reputation: 3

Excel 2013 Data Validation

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)

Upvotes: 0

Views: 180

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

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

Upvotes: 1

Related Questions