The following concepts are the key to validation usage.

The scope of the cells to be tested for validation is determined by the selection area when you create the validation, and all validation testing formulas created are based on the formula you enter which is relative to the activecell when you create the validation -- watch your absolute and relative references relative to the activecell.Filters, Validation, and Conditional Formatting are very similar to one another. The purpose of the formula is test a condition and return either True or False, and act upon it if the condition is True.

Your choices are:Any value, whole number, decimal,list, date, time, text length,custom.Most of those choices are obvious. List is shown below. Custom means using a formula and some examples are shown below under Validation Formulas.

For the unfortunate users of Excel 2007, you will find Validation in the Ribbon under Data then by Data Tools. Or as Microsoft would say "Data validation options are located in the Data Tools group.". Of course you practically have to start from there to get to Help, which is downloaded and not on your system. Nothing hurts like doing away with menus and in application help. You can't read HELP like a book anymore, and search brings up articles not the main topics in Table of Contents.

You can place specific values in anamed rangeand use that list for acceptable values.

Data, Validation, Settings Tab, Allow: List, Source: =namedrange, OK

(be sure to include that = sign in front of yournamed range)

When doing Data Validation you select a range, the formulas will be applied to the entire range based on the formula in the active cell, which would normally be the first cell in the range. A1 will be used for these examples.Data, Validation, custom, formula is:

Basic entries

Any value - range, whole number - range, Decimal- range, List, Date - range, Time -range, Text length - rangeDisallow spacesDave Peterson, misc, 2001-04-28

=LEN(A1)=LEN(SUBSTITUTE(A1," ",""))

=ISERROR(FIND(" ",A1))Disallow leading or trailing spaces, also disallows consecutive internal spaces

=LEN(A1)=LEN(TRIM(A1))Disallow ONLY CAPS

=NOT(EXACT(A1,UPPER(A1)))Allow only those found in a list

=NOT(ISERROR(MATCH(C1,$A$1:$A$5,0)))Allow only the negative amount of another cell

=ROUND(A1-A2,2)Allow only specific text choices

=VLOOKUP(CityGiven,StateFind,2) -- [more detail]

warning: "City not listed in table. Update the table and then re-enter the city on this sheet"Either of two choices

=OR(A1=1,A1=2)values 1 through 500, or 700 through 799

=OR(AND(A1>=1,A1<=500),AND(A1>=700,A1<=799))Unique value in a columnDavid Hagar EEE.004 (No Duplicates, Disallow Duplicate)

=COUNTIF($A$1:A1,A1)=1actually insufficient because it does not check the entire range, see next exampleUnique value in a rangeChip Pearson, Preventing Duplicates On Entry

=COUNTIF($A$1:$A$10,"="&A1)=1

=COUNTIF(tablename,A1)=1 -- equal is assumed «Prevent Duplicate Entries, Q313185 -- XL2000: How to Use Data Validation to Prevent Duplicate Entries

=ISERROR(MATCH(A1,A2:A50,0)) in formula, and

=ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0)) in settings

Haven't really looked at this but it requires two parts to do what David Hagar accomplishes with a single formula. Chip's solution/example is for a fixed range and as such is not applicable to more generic usage.Require Entry in a Specific Columnbefore allowing entry.

=NOT(TRIM(A1)="")Limit up to 25 characters

=LEN(A1)<=25 -- same as basic length validationChoose from one of three lists based on value in A1-- Alan Linton, programming, 2000/10/02

=Choose(A1,$A$3:$A$5,$B$3:$B$5,$C$3:$C$5)

=Indirect($c$1) where c1: =If(A1=1,"$A$3:$A$5","$B$3:$B$5","$C$3:$C$5")

=list1 where list1 refers to defined name formula: =IF(A1=1,$A$3:$A$5,$B$3:$B$5,$C$3:$C$5)Until SUM reaches a value

=SUM($A$1:$A$10)< 0 -- Harlan Grove 2000-01-11Weekday (Mon-Fri only)

=AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7) -- Debra Dalgleish, site

Avoid confusing identifications with actual numbers. If you leave out a leading zero on a US zip code it will be incorrect if you leave out a leading zero on a catalog number it will be incorrect.Trying to treat a 16 digit credit card number as an actual number which Excel limits to 15 digits is the most frequently encountered problem with 15 digit

specification Limit(see Help) in Excel. Treat your identification data as text. You can make up a validation rule to verify that you have a Credit Card Number with a correct syntax 16 digits with a valid check digit in the last position.Format the column as Text or preceed the identification with a single quote.

Myrna Larson on Credit Card mod 10 check digits

=ValidCard(F1)code below

http://google.com/groups?selm=e1qj7tg5utj42pvmtljd8rms91ddqqkmb2%404ax.com

http://google.com/groups?selm=ap314u4unls15otfk1fbggsb5tstqi6nn6%404ax.comAdditional information on Credit cards - some credit cards have less than 16 digits

http://www.beachnet.com/~hstiles/cardtype.html (found on a Google search)

- reserved for validations of US zip codes, Canadian postal codes, UK postal codes
- =AND(OR(Len(D4)=5,len(d4)=9),ISNUMBER(left(D4,5)& right(d4,4))) -- US
- =AND(LEN(D4)=7, left(d4)>="A", ISNUMBER(mid(d4,2,1)& mid(d4,5,1) & right(d4)) -- Canadian
- =AND(left(d4)>="A", ...)

Data validation only applies to keyed in entry, at the time of entry. Constants are checked at entry. Formulas entered are evaluated only at the time of entry in regards to validation. [Excel 2000 is what I use]Pasting a value into a cell with validation will not be validation checked, nor will cell changed with Replace (ctrl+h).

**Count of unique entries in a range**

=SUMPRODUCT(1/(COUNTIF(A1:A15,A1:A15))) -- Tom Ogilvy ( Feb 10 2001) -- must be no blank cells**Identification**, see Chip Pearson's Duplicate and Unique Items in Lists

http://www.cpearson.com/excel/duplicat.htm

http://www.mvps.org/dmcritchie/condfmt.htm (Conditional Formatting)**Prevention**with validation,

http://www.cpearson.com/excel/NoDupEntry.htm

http://www.cpearson.com/excel/named.htm#Cheat

http://www.mvps.org/dmcritchie/excel/validation.htm**Delete Duplicate Rows**

http://www.cpearson.com/excel/deleting.htm**Filtering for Unique entries**, You can eliminate duplicate by use of a Filter

Data, Filter, Advanced, Unique only

Filtering does not eliminate duplicates but the filtered cells can be pasted, printed, as if they are the only data.**Restricting Input before bringing into Excel**

From a database use of Distinct in SQL, an article Eliminating duplicate rows in Excel - TechSpace - Express Computer India, Dr. Nitin Paranjpe, article uses little used feature in Excel ODBC to enter SQL, another part of the artilce uses ODBC to eliminate ASCII unwanted data.

Importing ASCII (flat file) using a macro and eliminating unwanted data.

- There is a good example of simple cell validation in the "Data Validation" sheet of
**samples.xls**that came with your intallation of Excel. - DataVa101 and DataVa1021 «, Data Validation lists, Debra Dalgleish, Contextures.com -- http://www.contextures.com/xlDataVal01.html
- Excel -- Data Validation -- AdvancedTechniques, Debra Dalgleish, make the font larger, or validation drop down wider.
- Data Validation, Using Cells On Other Sheets - Chip Pearson, to get around that validate won't allow you to supply a range on another sheet.
- Excel Developer Tip: Playing a Sound Based on a Cell's Value, tip 87, John Walkenbach
- Excel Developer Tip: Ensuring that Data Validation is Not Deleted «, tip 98, John Walkenbach
- Validate, search entered on John Walkenbach's Excel site. (an interesting sidetrip checklist for creating a form: tip 05).
- XL2000: How to Use Data Validation to Prevent Duplicate Entries, MSKB 213185
- Two validation lists used together, in cell B1 be able to pick the category "Flooring", then in C1 have the list of the Flooring contractors displayed for the user to pick. Posted Code by Damon Ostrander, 2002-04-29.
- How to get data validation messages to pop up automagically, Frank Isaacs,Dolphin Technologies.
- Event handlers could be disabled by disabling macros generally or disabling only event handlers. Harlan Grove, 2004-03-26 provides a means of determinining if macros are disabled and specifically if Event Handlers are disabled.
- Using a validation list in another worksheet or workbook, Microsoft Office Assistance.
- Validation into Comments and Comments to Validation Message conversions, for those who don't want to see comment indicator. Not recommended but can be done.

You are one of many distinguished visitors who have visited my site here or in a previous location since this page was created on April 28, 2001.

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie
*
send email comments*

Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved