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 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)
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 - range
- Disallow spaces Dave Peterson, misc, 2001-04-28
- Disallow leading or trailing spaces, also disallows consecutive internal spaces
- Disallow ONLY CAPS
- Allow only those found in a list
- Allow only the negative amount of another cell
- 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
- values 1 through 500, or 700 through 799
- Unique value in a column David Hagar EEE.004 (No Duplicates, Disallow Duplicate)
=COUNTIF($A$1:A1,A1)=1 actually insufficient because it does not check the entire range, see next example
- Unique value in a range Chip Pearson, Preventing Duplicates On Entry
=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 Column before allowing entry.
- Limit up to 25 characters
=LEN(A1)<=25 -- same as basic length validation
- Choose from one of three lists based on value in A1 -- Alan Linton, programming, 2000/10/02
=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-11
- Weekday (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
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)
- 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).
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