Creating a Data Entry and Validation System in Microsoft Excel “Excel 7-3: Data Entry and Formatting”

Open the PlacerHills-07 start file. Click the Enable Editing button. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. The #NUM! error appears in four cells because arguments for the formulas are missing.
Select the Agents sheet tab. Agent names and data display.
Find and clear data validation settings.
Click the Find & Select button [Home tab, Editing group] and choose Go To Special.
Select the option button for Data validation (Figure 7-77).
Figure 7-77 Go To Special dialog box
Click OK. No cells on the Agents sheet have validation criteria.
Click OK in the message box.
Click the Comparison sheet tab.
Click the Find & Select button [Home tab, Editing group] and choose Data Validation. Cells with validation settings are highlighted.
Click the Data Validation button [Data tab, Data Tools group], and verify the Settings tab is selected. The current criteria requires a whole number greater than or equal to 10.
Click Clear All on the Settings tab and leave the dialog box open.
Set validation criteria with an error alert.
Verify that the following cells in the worksheet are selected: C9:C12, H9, H11, H13:H14, and H16. (If they are not, close the Data Validation dialog box, select the cells, and click the Data Validation button [Data tab, Data Tools group].)
Click the Allow arrow and choose Decimal on the Settings tab.
Click the Data arrow and choose greater than or equal to.
Click the Minimum box and type 0.
Click the Error Alert tab and verify that the Show error alert after invalid data is entered box is selected.
Verify that Stop displays as the Style.
Click the Title box and type Required.
Click the Error message box and type Please enter a positive value. including the period (Figure 7-78).
Figure 7-78 Error Alert tab
Click OK.
Display the Developer tab on the Ribbon if it is not shown.
Select the Options command [File tab] and click Customize Ribbon in the left pane.
Select the Developer box in the Main Tabs group and click OK.
Insert a spin button form control.
Show the gridlines on the worksheet [View tab].
Click the Insert Controls button [Developer tab, Controls group].
Click the Spin Button (Form Control) button.
Draw a control directly over cells I21:I22 (Figure 7-79).
Figure 7-79 Spin button form control
Right-click the control and choose Format Control.
Click the Minimum value box on the Control tab and type 1.
Set a maximum value of 8. These are agent identification numbers (1 through 8). The incremental change is 1 so that each click of a spin button goes up or down a number.
Click the Cell link box and select cell H22. The choice made by the spin button will be placed in this cell (Figure 7-80).
Figure 7-80 Options set for spin button control
Verify that the 3-D shading box is selected and click OK. The linked cell displays the minimum value 1.
Click cell H23 to deselect the control.
Use an INDEX formula to display the agent’s name.
Verify that the active cell is H23.
Click the Lookup & Reference button [Formulas tab, Function Library group].
Choose Index and select the array, row_num, column_num argument group.
Click the Array box, click the Agents sheet tab, and select cells A2:A7.
Click the Row_num box, and select cell H22, and click OK.
Center align cell H23.
Turn off the gridlines.
Protect the worksheet for data entry.
Click the Find & Select button [Home tab, Editing group] and choose Data Validation. The cells are selected.
Press Ctrl and select cells D4:E4, H22:H23, and E22 to add them to the selection.
Click the Format button [Home tab, Cells group].
Select Lock Cell to remove the Locked property.
Delete the contents of cells C9:C10, H9 and H16.
Select cell D4 to position the insertion point on the worksheet.
Click the Protect Sheet button [Review tab, Protect group].
Allow the options to select locked and unlocked cells.
Do not use a password and click OK.
Select cell C9, type 450000, and press Enter. The cells are formatted and you need not type any symbol or decimal point unless there is a significant decimal amount.
Type 275000 in cell C10 and press Enter.
Select cell H9, type 485000, and press Enter.
Type 75000 in cell H11.
Enter data in cells H13, H14, and H16 as shown in Figure 7-81. The term is 30, the rate is 3.15%, and estimated taxes and insurance are $1200.
Figure 7-81 Excel 7-3 completed
Click the spin box control arrows to select 3 as the agent ID.
Remove the Developer tab.
Click the Options button [File tab] and click Customize Ribbon in the left pane.
Deselect the Developer box in the Main Tabs group and click OK.
Save and close the workbook (Figure 7-81).
Upload and save your project file.
Submit file for grading.