Overview and samples of data validation Web Page
List From Another Worksheet
On Off Validation
Updating Validation List
Dymanic Named Ranges Web Page
Back to Index Intro
Validation is feature of Excel that was introduced in Excel 97. Since its inception it has become extremely popular
The purpose of Validation is to try and ensure a user does not enter invalid data into a cell or a range of cells.
To use Validation you go to Data>Validation via the Worksheet Menu Bar.
This Workbook shows various examples of how Validation can be used for different user needs
See: Overview and samples of data validation
All Blue Cells In the Workbook have Validation applied. Select any of these cells and choose
Data>Validation via the Worksheet Menu Bar to see the criteria used.
has become extremely popular
nto a cell or a range of cells.
hese cells and choose
Back to Index
Enter A Whole Number Between 1 And 10.
Enter A Decimal Number Greater Than 2.5
Select Entry From The List
Select Entry From The List 2
Enter A Valid Date Equal To, Or Greater Than 01/01/04
Enter A Valid Time Not Between 05:00 AM And 11:00 AM
Enter Data That Has 5 Characters
To see Validation settings
Select the cell below the heading
and go to Data>Validation
Enter data ouside the range to see
And 11:00 AM
Back to Index Custom Option
As long as the formula evaluates to True, the numbers entered into A2:A19
will be accepted
Year 2005 All Formulae used in the Custom option of Data>Validation
$ 256.32 must evaluate to True or False
$ 258.00 TIP: You will often find it easier to first place your formula into any cell
$ 56.32 The cell below contains the formula used in Validation of A6:A23
$ 356.21 TRUE All gray cells house the formula used in the Validation Custom
$ 566.00 option
$ 324.11 Example 2 Example 3 Example 4
$ 258.36 Enter Data Below Headings Enter any data below Stop Duplicates
$ 1,000.00 Name Age Dave
TRUE TRUE FALSE FALSE John
FALSE FALSE Harry
Names Department Example 6 Example 7
Fred Finance Number Text
Mary Finance FALSE FALSE
$ 3,400.95 Bob
Example 8 Next >>
Data << Previous
TRUE TRUE Cats TRUE
TRUE TRUE TRUE
TRUE TRUE FALSE
FALSE FALSE FALSE
FALSE FALSE FALSE
FALSE FALSE FALSE
Back to Index List From Another Worksheet
Normally, Excel will not allow you to use the List option when the List is on another Worksheet.
We can work-around this in 2 ways.
1: Name our list range, then use the Name as the List Source
2: Place the referenced List Range inside the INDIRECT Function
Select the blue cell(s) and go to Data>Validation
1: Named Range Method 2: Indirect Method
The list for both Validated cells above resides on a Worksheet named Hidden Lists. The Sheet is hidden, to
see it go to Format>Sheet>Unhide
The Sheet is hidden, to
Back to Index Linked Lists
Fruit Fruit Vegetable Sweets Building Products
Vegetable Apples Carrots Peppermints Nails
Sweets Oranges Lettuce Chocolates Hammers
Building Products Pears Cabbages Sherbet Saw
Bananas Onions Ice Cream Bricks
Choose Category Choose Associated Item
How it Works
The range A3:A6 has been Named "Categories"
The lists below the Item heading have been Named the same as their headings.
The Create names option via Insert>Name>Create is a quick & easy way to name ranges.
Example: Select B3:E7 and go to Insert>Name>Create. Ensure only Top Row is checked and click OK.
In the case of: Building Products an Underscore is added in place of the space, that is Building_Products. This
is because Named Ranges cannot contain spaces.
The INDIRECT function is used in the List option of Validation to force Excel to see any text as a Range Name
and not Text.
The SUBSTITUTE Function has also been used in the Validation for C11 to replace any spaces with the underscore
See Excel help on the INDIRECT and SUBSTITUTE Function
checked and click OK.
t is Building_Products. This
any text as a Range Name
any spaces with the underscore
Back to Index On Off Validation
Nomally when Validation has been applied one would be required to remove the Validation so that data, outside
any validated range, can be entered. This method shows how we can use a CheckBox (from the Forms toolbar) to
quickly and easily toggle validation on and off.
FALSE Turn Validation On
Any Entries Formulas Used To see the Checkboxes Cell Link ($A$9 ), right click on it and
TRUE choose "Format Control" then click the "Control" page tab.
TRUE While the CheckBox is checked (TRUE) only numbers can be
TRUE entered into A11:21 . While the CheckBox is not checked (FALSE)
TRUE any entries (text or numbers) can be entered into A11:21 .
TRUE Next >>
TRUE << Previous
so that data, outside
he Forms toolbar) to
ght click on it and
ntrol" page tab.
y numbers can be
s not checked (FALSE)
ed into A11:21 .
Back to Index Updating Validation List
One draw-back with the List option of Validation is that there is no standard way for a user to add new
list entries without adding them to the List first, then selecting from the List. This method uses some
VBA code and an Dynamic Named Range to overcome this.
Dymanic Named Ranges (Web Page)
To see the VBA code, right click on the sheet name tab and select View Code
To see the Dynamic Named Range (Names) go to Insert>Name>Define
Enter any Name not in the "Names" list Dave
Although the cell above (A13) has Validation applied the June
"Show error alert when inavlid data entered" is not Jill
checked. This is so the VBA code can do the validating. Bill