You will find a few examples of E xcel data validation formulas to allow only numbers or text values in specific cells, or only text beginning with specific characters, permit unique data preventing duplicates, and more. The formula is in column B and the test values are in column D and E.The tutorial shows how to make custom Data Validation rules in Excel. This is an image showing the results of the formula.
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. 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.įor example, if you have Women as your D6 value, you can then enter 9000 as a value in E6. When you enter the number into E6 it will perform the data validation check. Now there are some limitations to watch out for. If you want to customize your error messages, select the Input Message tab In the Data Validation dialogue box, select custom from the Allow: drop down box, and paste the above formula in the formula box. 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: If your string was longer such as "Women with purple hair" then the above formula would need adjusting. Now I was also assuming that Men or Women was the only part of the string. 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.
0 kommentar(er)
