Question: One of the problems I face as the payroll administrator is that when entering the salaries for staff members, I sometimes enter figures outside the respective salary bands. Our employees are categorized into various grades which then determine the salary bracket. Is there a way of adding some validation so that when the wrong figure for the salary band is captured, an error message will be displayed?
Answer: That can be done by using – Data Validation
Why: To enter figures within a specified range for a given data set. For instance in the scenario given the salary band for Grade 1 = $17 000 – $20 000 & Grade 2 = $20 000 – $23 000
Applies To: Excel 2010, Excel 2007, Excel 2003
1. Enter data as given in the example below
2. Select the data range D5:D8
3. Click on the Data Ribbon and select Data Validation under Data Tools as given below.(Excel 2007 & Excel 2010)
For Excel 2003 refer to the screen shot below;
4. The following screen will be displayed
5. Adjust the settings as given above
6. Click on the Input Message Heading. Make changes as displayed below
7. Click on the Error Alert Heading and make the necessary changes as given below
8. Click OK
9. To enter the salaries for Grade 2 employees, repeat steps 2-8. However the data range will be E5:E8 and the salary band $20 000 – $23 000.
The validation will help in entering the correct salaries figures. If the wrong salary figure is entered an error message will be displayed to that effect. Because of the settings under Input message heading an appropriate message will be displayed when a cell within the given data range is selected.
