 |
| Using Data Validation Invalid and inconsistent data is one of most common problems in Spreadsheets. If the data supplied is not in the right format or is not entered consistently then any following formula's or calculations will either not work or worse still provide a result that is not correct. Breaking News - Subscriber Special - Message Blaster
Upgrade !!
50% off licence cost until the end of September, 2010 Click Here to find out more and use the Coupon Code (CPN2967064485) during your purchase to receive the Discount Excel provides a built-in Data Validation feature that can be included to any spreadsheet. Data Validation provides error checking and instructions for the user as they enter data to a spreadsheet. In this newsletter we will demonstrate some simple examples of Data Validation on a customer address list. Step 1 We start with a basic list of customers and addresses, we want to make sure that the values supplied for "Post/Zip Code" and "Country" are valid values and consistently entered to the list. We will use Data Validation on Column D and Column E to ensure that this happens for each new entry.  Step 2 We will now create a Data Validation rule for the "Post/Zip Code" column to ensure that only a valid whole number can be entered to those cells. Select the first cell in the column D7 and then to access Data Validation click on the "Data Tab" then click on Data Validation. For this validation select "Whole Number" and "Greater Than" to ensure that all entries in the cell are numbers greater than 0. Then click OK and the Data Validation will be applied for that cell.  Step 3 Now to test the validation for that cell try and enter something other than a whole number. The following error message will be returned. Later in the newsletter I will demonstrate how to customise this error message to give specific instructions to the user on the error that has occurred.  Step 4 Now we will include Data Validation for the Country column. This validation will be based on a list of countries in our spreadsheet. In another worksheet a list of countries is developed and named "ReferenceCountryList".  Step 5 Back on the Customer List worksheet we select the first cell in the Country column which is E7, we then select the Data Tab, Data Validation and specify a "List" from the options for validation. In the source we specify the name of the country list we created earlier.  Step 6 Now when that cell is selected a "Drop Down" option is presented for selection. The user can only select a value from the list or they receive the standard error message. Step 7 We can also customise the "Input Instructions" and "Error Message" that is presented to the user for any cell that has Data Validation. Access the Data Validation options for the "Post/Zip Code" cell D7. Then for the "Input Message" include any details that need to be described to the user for completion. For this demonstration I have included the following...  Which results in the following display on the worksheet when the user has selected that cell for entry  Step 8 We can also customise the "Error Message", on the Data Validation dialog box specify the following  Which shows as the following error message when the user provides an invalid entry.  Step 9 Lastly we need to make sure that all cells in the columns that need validation have the required validation in place. To do that select cell D7 and E7, then select Copy, now select all the cells that require the Data Validation, Right Click on the selection and select Paste Special. Then selection "Validation" and the Data Validation will be applied to all cells in that selection. Click
on the following
link to download a copy of this sample Data Validation Workbook
This is just one example of how Data Validation can be used to ensure your spreadsheets are error free and work. If you would like any advice or assistance in implementing Data Validation for one of your workbooks then please Contact Us and one of our consultants will step you through the design and implementation. Subscriber Special !!
25% off all Consulting until the end of September, 2010 Contact Us now and mention this newsletter offer to secure your discount If you are not yet a subscriber to the XLAutomation newsletter then Click Here to subscribe and receive guidance and tools that will increase your efficiency in using Microsoft Excel. |
Message Blaster... The XLAutomation development team have been hard at work and have just released a new version of Message Blaster with a host of new improvements. Click Here to find out more about the new release. As a special subscriber offer use the following coupon code CPN2967064485 to receive a 50% discount on any Message Blaster purchase until the end of September, 2010. Latest Offers "Free Consultation" Take advantage of our 1 hour free on-site consultation. We guarantee to provide advice that will improve the way you currently use your Microsoft® Office software. Click Here to book a session with one of our consultants. |
| |