Microsoft Excel: Avoiding Data Entry Errors With Dependent Data Validation Lists

Using data validation to create drop-down lists is a fabulous way to avoid data entry errors in your spreadsheets. In this article I want to take drop-down lists one step further. Let's imagine that you are creating a time sheet that needs to have departments and employee names included. You could create two totally separate lists, one with all of your departments and one with all of your employees, but that would allow you to accidentally put an employee into the wrong department. Instead, you can create a dependent validation list, so the only employees you will see are the ones that match the department you have chosen.

As with any data validation list, you need to begin by creating the options that will appear in the drop-down lists. I recommend doing this on another worksheet in your workbook and then creating named ranges. For more information about doing this, see my Ezine article 'Avoid Data Entry Errors by Using Drop-Down Lists.'

Start by creating a database with the departments across the first row and the employees listed in the appropriate cells underneath. Choose the cells in the first row and name the range 'Departments.' You now need to name the range of cells that contain the employees. A quick way to do this is as follows:

1. Select your database
2. Ctrl + Shift + F3
This should open the 'Create Names from Selection' dialog box.
3. Make sure there is a check mark in the location of your headers - in this case 'Top Row'
4. OK

If you click on the arrow beside the name box (to the left of the formula bar), you should see the named range 'Departments' as well as the name of each department.
Go to the cell of the sheet you want the list to appear in. We will start by creating a normal data validation list to show the departments:

1. Make sure you are on the cell, or cells, you want the drop-down for the departments to appear in
2. On the Data ribbon click on the top half of the Data Validation button in the Data Tools group
3. In the Data Validation dialog box, choose List for the value to Allow
4. In the source box, type '=' and then 'Departments,' or whatever name you used for your range
TIP: To see all the ranges in your workbook, press the F3 key. You can then choose the appropriate range from the box.
5. OK
6. Select a cell that you added this validation to and make sure the list of departments show when you click on the drop-down arrow
If this has worked, continue with the following steps:
7. Choose the cell, or cells, that you want to contain the drop-down of employee names
8. On the Data ribbon click on the top half of the Data Validation button in the Data Tools group
9. In the Data Validation dialog box, choose List for the value to Allow
10. In the source box type '=' and then 'Indirect'
This function will look for a named range that matches the value that is in the Departments cell. If you chose 'Sales,' Indirect will be looking for a range of cells that are named 'Sales.'
11. Behind the function name 'Indirect,' type an opening bracket, the cell reference for the first cell in the 'Departments' range, and then a closing bracket
Ensure that the 'Indirect' function cell referencing is not absolute. If it is, remove the dollar signs. An example of what you typed may be '=Indirect(A1)'
12. Click on a cell in the departments column and choose the desired option
13. Click on the employee cell in the same record and select the drop-down arrow
You should see only the names of the employees that are in that department.
TIP: If your list changes frequently, turn your database into a Table. This will create a dynamic range for your spreadsheet. Whenever someone is added to or removed from the table, the change will be reflected in the data validation lists.

Tired of people choosing an apple variety when they are filling in an order for cherries? Put dependent lists to work and create accurate data in your spreadsheets.

Reen Rose is an empowerment expert who believes in aiding businesses of all sizes, by helping individuals and teams acquire the skills they need to be happy and successful in their work. She is an experienced Microsoft Certified Trainer, Microsoft Office Specialist Master and a Myers Briggs certified practitioner.

She has been described as 'focused, fun and very effective'.
To find out more about Reen and the training and corporate services she offers, please visit her website at http://www.ReenRose.com.

Article Source: http://EzineArticles.com/?expert=Reen_Rose

Article Source: http://EzineArticles.com/8518691