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))

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 ©2011
All rights reserved.

Debra Dalgleish

Last updated: September 17, 2011 9:42
AM