Unit 22: Advance Validation

Data validation to allow / disallow certain types of data to be entered within a spreadsheet

  • Using Data Validation to choose a value for a cell from a dropdown list predefined with a list of data with a defined name

  • Formatting certain cells to only accept a date value with parameters

  • Returning Error Messages if Data Validation is not passed for a particular cell value

Data Validation When creating a form in excel, or using fields to set criteria or parameters for a query, it may be important to limit the type of data a user us allowed to key into the field. The Data Validation function in Excel limits the user’s ability to fill in a field by setting criteria on the cell. On the toolbar, the Data Validation option is found under the DATA tab – Data Validation.


The easiest way

Using literal dates is the simplest way to create a validation control that limits input to a range of dates. You just enter the first and last acceptable dates as follows:

  1. Select the cell to which you’re adding the validation control. (For this example, I’ll use cell C4 and add a fill color so it’s easy to spot.)

  2. Click the Data tab.

  3. In the Data Tools group, click Data Validation.

  4. In the resulting dialog, choose Date from the Allow dropdown. Doing so updates the dialog. The Data control defaults to Between, which is what we want, so don’t change that setting.

  5.  In the Start Date control, enter the first date in the period.

  6. In the End Date control, enter the last date in the period (Figure A).

  7. Click OK.

As you can see in Figure B, the control rejects a 2018 date. The setting is inclusive of both the start and end dates.

This is the simplest way to designate a range for a data validation control. There’s nothing wrong with taking the easy route. It’s easy to implement and easy to modify—simply select the cell, repeat the above steps, and change one or both dates. In addition, we used the Between operator to represent a range of dates—a time period. There are several operators; you should review them so you’ll know what’s available.

How to handle a data validation alert When you try to enter or change data in a worksheet cell, you see a data validation error alert. This alert signifies that the owner of the workbook applied data validation to the cell to prevent users from entering invalid data, and implemented the error alert to let you know that the data you entered is invalid. You can enter only valid data in cells that have data validation applied. If you are not clear about the validity of the data that you can enter, you should contact the owner of the workbook. If you inherited the workbook, you can modify or remove the data validation unless the worksheet is protected with a password that you do not know. If possible, you can contact the previous owner to help you unprotect the worksheet. You can also copy the data to another worksheet, and then remove the data validation.

UNIT 22 – Advance Validation

There aren't any posts currently published in this category.