Document Sample
Validation Powered By Docstoc
					            Worksheets Index
        Overview and samples of data validation Web Page

The Basics

Custom Option

List From Another Worksheet

linked Lists

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.

           Next >>
           << Previous
 has become extremely popular

nto a cell or a range of cells.

hese cells and choose
                    The Basics
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

Next >>
<< Previous

                                                        Next >>
                                                        << Previous
               To see Validation settings
           Select the cell below the heading
           and go to Data>Validation
           Enter data ouside the range to see
           error messages

And 11:00 AM

           Next >>
           << Previous
Back to Index                           Custom Option
As long as the formula evaluates to True, the numbers entered into A2:A19
will be accepted
   Example 1
Year 2005         All Formulae used in the Custom option of Data>Validation
 $      256.32    must evaluate to True or False
 $      125.63
 $      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
 $      200.00
 $      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
                            Example 5
                   Names         Department          Example 6 Example 7
                  Fred       Finance                  Number            Text
                  Bill       Marketting
                  Mary       Finance                    FALSE          FALSE
 $ 3,400.95       Bob
                                                   Example 8                     Next >>
                                                      Data                       << Previous
                                                   Horses           TRUE
                    TRUE            TRUE           Cats             TRUE
                    TRUE            TRUE                            TRUE
                    TRUE            TRUE                            FALSE
                    FALSE           FALSE                           FALSE
                    FALSE           FALSE                           FALSE
                    FALSE           FALSE                           FALSE
Stop Duplicates
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

Next >>
<< Previous
The Sheet is hidden, to
Back to Index                                  Linked Lists
   Categories                                     Items
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

Next >>
<< Previous
ng Products

 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

<< Previous