Validate / Validation

Location: http://www.mvps.org/dmcritchie/excel/validation.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Validation scope

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.

Validate on Data menu - Validation list

You can place specific values in a named range and use that list for acceptable values. 
    Data, Validation, Settings Tab, Allow: List, Source: =namedrange, OK
    (be sure to include that = sign in front of your named range)

Validate on Data menu - Validation formulas

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:

Credit Card validation, mod 10, check digit

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.com

Additional information on Credit cards - some credit cards have less than 16 digits
  http://www.beachnet.com/~hstiles/cardtype.html (found on a Google search)

Postal and Zipcode validations

Some notes on Validation

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

Validation in VBA

see validation in VBE HELP.  Looking for a means to see the formuala used.

Duplicate Entries (identification, prevention, elimination, deleting, filtering)

Validation Related Articles


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