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