ALS Management Information System
Data Validation Manual
(Updated on December 2015)
Table of Contents
What is data validation? 2
When is data validation useful? 2
Ensure your Spreadsheet is Ready 3
· To activate the Developer Tab in Ribbon 3
· To enable macros 3
Do not forget! 3
Rename your file 3
Validation Criteria and Formulas 4
· Restrict text length – For all text cells 4
· Require a unique 12-digit number – for LRN 4
· Require whole numbers (no decimal) – For Zone and Type of CLC 5
· Restrict the range of whole numbers – for FLT scores 6
· Require a specific range of dates - Birthdates 8
· Require the first letter of each word to be capitalised – for entered text 8
The Types of Data Validation Messages 10
· Input Message 10
· Error Alert 10
There are 3 types of error alerts 11
How to create an Error Alert? 11
· Error Alert Message for Text Length 12
· Error Alert Message for Requiring a Unique 12-Digit Number 13
· Error Alert Message for Requiring Whole Numbers (No Decimal) 13
· Error Alert Message for Restricting the Range of Whole Numbers 14
· Error Alert Message for Requiring a Range of Dates 14
What is data validation?[1]
Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.
When is data validation useful?[2]
Data validation is invaluable when you want to share a workbook with others in your organization, and you want the data entered in the workbook to be accurate and consistent.
Among other things, you can use data validation to do the following:
1. Restrict data to predefined items in a list
For example, you can limit types of departments to Sales, Finance, R&D, and IT. Similarly, you can create a list of values from a range of cells elsewhere in the worksheet.
2. Restrict numbers outside a specified range
For example, you can specify a minimum limit of deductions to two times the number of children in a particular cell.
3. Restrict dates outside a certain time frame
For example, you can specify a time frame between today's date and 3 days from today's date.
4. Restrict times outside a certain time frame
For example, you can specify a time frame for serving breakfast between the time when the restaurant opens and 5 hours after the restaurant opens.
5. Limit the number of text characters
For example, you can limit the allowed text in a cell to 10 or fewer characters. Similarly, you can set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1), plus 10 characters.
6. Validate data based on formulas or values in other cells
For example, you can use data validation to set a maximum limit for commissions and bonuses of $3,600, based on the overall projected payroll value. If users enter more than $3,600 in the cell, they see a validation message.
Ensure your Spreadsheet is Ready
To make sure that the data validations are working on any spreadsheets, the users will have to show the Developer Tab in their ribbon and to ‘enable all macros’ in their settings.
To do so, please follow the instructions.
· To activate the Developer Tab in Ribbon
1. Go to Office button on the top left corner
2. Click on Excel Options on the bottom right corner
3. In Popular: Top options for with Excel
4. Tick the box for Show Developer tab in the Ribbon
5. OK
Depending on your version of Microsoft, please follow these steps:
1. Go to File on the top left corner
2. Click on Options at the bottom
3. Customize Ribbon
4. Tick the box for Show Developer tab in the Ribbon
5. OK
· To enable macros
1. Go to Office button on the top left corner
2. Click on Excel Options on the bottom right corner
3. In Trust Centre: Under Microsoft Office Excel Trust Centre, click on the Trust Centre Settings button
4. In Macro Settings: select Enable all macros (not recommended; potentially dangerous code can run)
Depending on your version of Microsoft, please follow these steps:
1. Go to File on the top left corner
2. Click on Options at the bottom
3. In Trust Centre: Under Microsoft Office Excel Trust Centre, click on the Trust Centre Settings button
4. In Macro Settings: select Enable all macros (not recommended; potentially dangerous code can run)
Do not forget!
Save the change and close the spreadsheet and open it again for have access to your new set ups.
Rename your file
Please see below how your file should be renamed:
YYYY(space)MIS(space)District(space)Designation(space)Surname
Example: 2015 MIS Pili MT Pagaran
Validation Criteria and Formulas
· Restrict text length – For all text cells
1. Select one or more cells to validate
2. Go to the Data tab
3. On the Data tab, in the Data Tools group, click on Data Validation
4. In the Data Validation dialog box, find Settings
5. In Settings, provide Validation Criteria (see as follows)
Allow: Text length
Data: Between
Minimum: 1
Maximum: 20
Click Ok
· Require a unique 12-digit number – for LRN
Select one or more cells to validate
Go to the Data tab
On the Data tab, in the Data Tools group, click on Data Validation
In the Data Validation dialog box, find Settings
In Settings, provide Validation Criteria (see as follows)
Allow: Custom
Formula: =AND(LEN(L2)=12,ISNUMBER(L2))
Please note: Enter this formula for the first cell of the column (here, cell L2). Select the cell in your workbook and drag the selected cell along the rest of the column.
· Require whole numbers (no decimal) – For Zone and Type of CLC
1. Select one or more cells to validate
2. Go to the Data tab
3. On the Data tab, in the Data Tools group, click on Data Validation
4. In the Data Validation dialog box, find Settings
5. In Settings, provide Validation Criteria (see as follows)
Allow: Whole number
Data: between
Minimum: 1
Maximum: 7 (or 4 for Type of CLC)
OK
· Restrict the range of whole numbers – for FLT scores
1. Select one or more cells to validate
2. Go to the Data tab
3. On the Data tab, in the Data Tools group, click on Data Validation
4. In the Data Validation dialog box, find Settings
5. In Settings, provide Validation Criteria (see as follows)
Allow: Whole number
Data: between
Minimum: 0
Maximum: 100
OK
· Require a specific range of dates - Birthdates
1. Select one or more cells to validate
2. Go to the Data tab
3. On the Data tab, in the Data Tools group, click on Data Validation
4. In the Data Validation dialog box, find Settings
5. In Input Message, type your message (see as follows)
· Require the first letter of each word to be capitalised – for entered text
NOTE: Please note that this is not a data validation rule but a code applied here.
1. Select one
2. Go to the Developer tab
3. On the Developer tab, in the Visual Basic group, click on Editor
4. Double-click on Sheet you are working on
5. Select Worksheet in the first drop-down (top left corner)
6. Select Change in the second drop-down (top right corner)
7. Write your code on the blank space (see as follows)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Forces text to Proper case for the range D2:D50
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("D2:D50")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
To learn how to type this code into the Visual Basic Editor, please watch this Youtube video: https://www.youtube.com/watch?v=7RVGdA9AhrY&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5&index=2
No need to develop an Error Alert Message as the entered data will be automatically changed to the correct text format (as set up by the code).
Please be aware that you cannot simply copy and paste the piece of code from this document for it to work on your Excel spreadsheet. You will have to type the code into the Visual Basic Editor window.
The Types of Data Validation Messages
What users see when they enter invalid data into a cell depends on how you have configured the data validation.
· Input Message
You can choose to show an input message when the user selects the cell. This type of message appears near the cell. You can move this message, if you want to, and it remains until you move to another cell or press ESC.
Input messages are generally used to offer users guidance about the type of data that you want entered in the cell.
· Error Alert
You can also choose to show an error alert that appears only after users enter invalid data.
There are 3 types of error alerts
Icon / Type / Use to/ Stop / Prevent users from entering invalid data in a cell. A Stop alert message has two options: Retry or Cancel.
/ Warning / Warn users that the data they entered is invalid, without preventing them from entering it. When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry.
/ Information / Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it.
You can customize the text that users see in an error alert message. If you choose not to do so, users see a default message.
How to create an Error Alert?
1. Select one or more cells to validate
2. Go to the Data tab
3. On the Data tab, in the Data Tools group, click on Data Validation
4. In the Data Validation dialog box, click Input Message
5. In Input Message, unselect the automatically selected Show input message when cell is selected
1. Go to Error Alert, select Show error alter after invalid data is entered
2. Select the Style, Stop
3. Enter a Title and an Error message (see example below)
· Error Alert Message for Text Length
· Error Alert Message for Requiring a Unique 12-Digit Number
· Error Alert Message for Requiring Whole Numbers (No Decimal)
· Error Alert Message for Restricting the Range of Whole Numbers
· Error Alert Message for Requiring a Range of Dates
14
[1] (Source: https://support.office.com/en-nz/article/Apply-data-validation-to-cells-c743a24a-bc48-41f1-bd92-95b6aeeb73c9)
[2] (Source: https://support.office.com/en-nz/article/Apply-data-validation-to-cells-c743a24a-bc48-41f1-bd92-95b6aeeb73c9)