Search Contextures Sites

Excel Data Validation Examples Custom Criteria

Prevent Duplicates With Data Validation
Limit the Total
No Leading or Trailing Spaces
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..

  1. Select cells B3:B10
  2. Choose Data|Data Validation
  3. Choose Allow: Custom
  4. 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
   data validation 01


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

  1. Select cells C3:C7
  2. Choose Data|Data Validation
  3. Choose Allow: Custom
  4. 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

  data validation 02

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. The formula in this example checks that the entry in cell B2 is equal to the trimmed entry in that cell.

  1. Select cell B2
  2. Choose Data|Data Validation
  3. Choose Allow: Custom
  4. For the formula, enter:
       =B2 = TRIM(B2)

  data validation 03

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.

  1. Select cell B2
  2. Choose Data|Data Validation
  3. Choose Allow: Custom
  4. For the formula, enter:
  data validation 04
=AND(WEEKDAY(B2) <> 1, WEEKDAY(B2) <> 7)

Excel Data Validation Tutorials

1. Data Validation Basics
2. Data Validation -- Create Dependent Lists
3. Hide Previously Used Items in a Dropdown List
4. Display Messages to the User
5. Use a List from Another Workbook
6. Validation Criteria Examples
7. Custom Validation Criteria Examples
8. Data Validation Tips
9. Data Validation Documentation   
10 Data Validation -- Combo box     
11. Data Validation -- Combo Box - Named Ranges
12. Display Input Messages in a Text Box 
13. Dependent Dropdowns from a Sorted List  
14. Data Validation -- Combo Box -- Click
15. Data Validation -- Create Dependent Lists With INDEX  

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Last updated: December 22, 2009 2:14 AM