Search Contextures Sites

Excel Data Validation Examples Custom Criteria

Prevent Duplicates With Data Validation Limit the Total No Leading or Trailing Spaces No Spaces in Text Prohibit Weekend Dates

Prevent Duplicates With Data Validation

You can use Excel Data Validation to prevent duplicate entries in a range on the worksheet. In this example, Employee Numbers will be entered in cells B3:B10, and you can prevent the same number from being entered twice.. Select cells B3:B10 Choose Data|Data Validation Choose Allow: Custom For the formula in this example, we use the COUNTIF function to count the occurrences of the value in cell B3, in the range $B$3:$B$10. The formula's result must be 1 or 0:    =COUNTIF($B$3:$B$10,B3) <= 1
Limit the Total Prevent the entry of a value that will cause a range to exceed a set total. In this example, the total budget cannot exceed $3500. The budget amounts are in cells C3:C7, and the formula checks the total in those cells Select cells C3:C7 Choose Data|Data Validation Choose Allow: Custom For the formula, use SUM to total the values in the range $C$3:$C$7. The result must be less than or equal to $3500:    =SUM($C$3:$C$7) <= 3500
No Leading or Trailing Spaces You can prevent users from adding spaces before or after the text in the entry. The TRIM function removes spaces before and after the text, and any extra spaces within the text. The formula in this example checks that the entry in cell B2 is equal to the trimmed entry in that cell. Select cell B2 Choose Data|Data Validation Choose Allow: Custom For the formula, enter:    =B2 = TRIM(B2)
No Spaces in Text Thanks to Jerry Latham for this example. You can prevent users from adding ANY spaces in a text string. The SUBSTITUTE function replaces each space character – " " – with an empty string – "" The formula in this example checks that the entry in cell B3 is equal to the entry without spaces. Select cell B3 On the Ribbon, click the Data tab, then click Data Validation Choose Allow: Custom For the formula, enter:    =B3=SUBSTITUTE(B3," ","")
No Spaces in Cell You can prevent users from adding ANY spaces in the cell -- whether the entry is text, numbers, or a combination of both. Here are two formulas that check for spaces. Example 1 Thanks to Jerry Latham for this example. The LEN function counts the number of characters entered in cell B3, and compares that to the number of characters after SUBSTITUTE removes the space characters. Select cell B3 On the Ribbon, click the Data tab, then click Data Validation Choose Allow: Custom For the formula, enter:    =LEN(B3)=LEN(SUBSTITUTE(B3," ",""))
Example 2 The FIND function looks for the space character – " " – and the ISERROR function result is TRUE, if the space character is not found. The formula in this example checks that the entry in cell B3 is equal to the entry without spaces. Select cell B3 On the Ribbon, click the Data tab, then click Data Validation Choose Allow: Custom For the formula, enter:    =ISERROR(FIND(" ",B3))
Restrict Date Range Instead of limiting dates to a specific date range, you can limit to within a specific number of days from the current date. In this example, the earliest start date allowed is 60 days prior to the current date: =TODAY()-60 The latest end date allowed is 60 days after the current date: =TODAY()-60
Prohibit Weekend Dates Prevent entry of dates that fall on Saturday or Sunday. The WEEKDAY function returns the weekday number for the date entered, and values of 1 (Sunday) and 7 (Saturday) are not allowed. Select cell B2 Choose Data|Data Validation Choose Allow: Custom For the formula, enter:

=AND(WEEKDAY(B2) <> 1, WEEKDAY(B2) <> 7)

Excel Tutorials - Data Validation

Data Validation Basics Data Validation - Create Dependent Lists Data Validation - Dependent Dropdowns from a Sorted List   Data Validation - Dependent Lists With INDEX   Hide Previously Used Items in a Dropdown List Data Validation - Display Messages to the User Data Validation - Display Input Messages in a Text Box  Data Validation - Use a List from Another Workbook Data Validation Criteria Examples Data Validation Custom Criteria Examples Data Validation Tips Data Validation Documentation Data Validation Combo Box      Data Validation Combo Box - Named Ranges Data Validation Combo Box -- Click Data Validation - Add New Items

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2013 All rights reserved.	Debra Dalgleish

Last updated: March 20, 2013 10:46 PM