Guided Project 7-2
In this project, you complete the purchase order for Blue Lake Sports by adding a data validation list and check box form controls. You protect the sheet and the workbook, save it as a template, and share it for testing.
File Needed: BlueLakeSports-07.xlsx (Student data files are available in the Library of your SIMnet account)
Completed Project File Names: [your initials] Excel 7-2Template.xltx, [your initials] Excel 7-2User1.xlsx, [your initials] Excel 7-2User2.xlsx, and [your initials] Excel 7-2Merged.xlsx
Skills Covered in this Project
- Set data validation to use a list.
- Use an error alert for data validation.
- Insert a check box form control.
- Unlock worksheet cells and protect a worksheet.
- Save a workbook as a template.
- Create a new workbook from a template.
- Share a workbook and track changes.
- Accept or reject changes.
- Open the BlueLakeSports-07 workbook from your student data files.
- Save the workbook as a template.
- Click the Save As button [File tab] and choose This PC.
- Click the More Options link.
- Type the file name [your initials] Excel 7-2Template in the File name box.
- Verify that the Save Thumbnail box is selected or select it.
- Choose Excel Template from the Save as type drop-down box.
- Click Save.
- Set data validation with an error alert to use a list.
- Click the Purchase Order sheet tab, select B17, and click the Data Validation button [Data tab, Data Tools group].
- Click the Allow arrow and choose List on the Settings tab.
- Click the Source entry box.
- Click the Departments worksheet tab and select cells A2:A14.
- Click the Error Alert tab.
- Verify that the Show error alert after invalid data is entered box is selected.
- Use Stop for the Style to prohibit an invalid entry.
- Click the Title box and type Wait!.
- Click the Error message box and type Please choose from the list.
- Click OK.
- Display the Developer tab and the Compare and Merge Workbooks button.
- Select the Options command [File tab] and click Customize Ribbon in the left pane.
- Select the Developer box in the Main Tabs group.
- Click Quick Access Toolbar in the left pane.
- Choose All Commands from the Choose commands from list.
- Click Compare and Merge Workbooks in the commands list.
- Click Add and click OK.
- Insert a check box form control.
- Click the View tab and check the Gridlines box in the Show group to draw and position controls with gridlines visible.
- Click the Insert Controls button [Developer tab, Controls group] and click the Check Box (Form Control) button.
- Draw a control directly over cell B15.
- page E7-476Click the Properties button [Developer tab, Controls group].
- Verify that the Unchecked radio button is selected on the Control tab.
- Select the 3-D shading box.
- Click the Protection tab and deselect the Locked box.
- Click OK.
- Click two times near the word “Check” with the control to place an insertion point.
- Delete Check Box 1, press Spacebar, and type Yes
- Click cell E15 to deselect the control. If you accidentally placed a check mark within the control, point and click to remove it.
- Copy a check box form control.
- Point to the check box control label Yes and right-click.
- Choose Copy from the menu and then press Ctrl+V to paste the control.
- Point to any border of the copied control to display a move pointer.
- Drag the copy to cell C15.
- Press Ctrl and right-click while pointing to the first check box control to select both controls (Figure 7-65).7-65 Two controls selected
- Click the Align button [Drawing Tools Format tab, Arrange group] and choose Align Middle.
- Select cell E15 to deselect both controls.
- Right-click the copied control and choose Edit Text to place an insertion point.
- Delete Yes and type No (Figure 7-66).7-66 Controls are aligned
- Select cell E15. Remove check marks that were accidentally entered.
- Protect the worksheet and the workbook structure.
- Select D7.
- Press Ctrl and select cell D9, cells B11:D13, cell B17, and cells A20:D28.
- Click the Format button [Home tab, Cells group].
- Select Lock Cell to remove the Locked property.
- Click cell D7.
- Click the Protect Sheet button [Review tab, Changes group].
- Allow the options to select locked and unlocked cells. Do not use a password.
- Click OK.
- Click the Protect Workbook button [Review tab, Changes group].
- Do not use a password and click OK.
- Save and close the template.
- Create a workbook from a template.
- Click the New button [File tab] and click PERSONAL near the top of the gallery.
- Click [your initials] Excel 7-2Template to create a workbook.
- Type BLS00120 in cell D7 and press Tab.
- page E7-477Type =to in cell D9, press Tab to select TODAY, and press Enter.
- Type the following in cells B11:B13:
- Outdoor Apparel, Inc
- 4232 South Water Street
- Omaha, NE 68107
- Select the check box control for Yes.
- Select cell B17, click the data validation arrow, and choose Apparel.
- Save the workbook as an Excel workbook named [your initials] Excel 7-2User1 in your usual location for saving files.
- Inspect the workbook.
- Click the File tab. You cannot inspect a worksheet that is protected.
- Click Unprotect in the Protect Workbook area
- Click the Check for Issues button and choose Inspect Document.
- Choose Yes to save the document before inspection.
- Click Inspect in the Document Inspector dialog box.
- Click Remove All to remove all personal information and document properties.
- Click Close.
- Check compatibility.
- Click the Protect Workbook button [Review group, Changes group]. In order to generate a compatibility report, the Protect Workbook property must be disabled.
- Click the Check for Issues button [File tab, Info group] and choose Check Compatibility.
- Click Copy to New Sheet.
- Click the Purchase Order sheet tab.
- Share the workbook.
- Click the File tab.
- Click Allow this information to be saved in your file in the Inspect Workbook group. This property was activated when you removed metadata; it prohibits sharing the workbook.
- Return to the Purchase Order sheet and click the Share Workbook button [Review tab, Changes category].
- Select the Allow changes by more than one user at the same time box.
- Click the Advanced tab and set the Keep change history for option to 15 days.
- Click OK to close the Share Workbook dialog box.
- Click OK to resave the shared workbook.
- Make a copy of the shared workbook in the same folder.
- Click the File tab and choose Save As.
- Select This PC and click the More Options link.
- Navigate to and choose your folder and save the workbook as [your initials] Excel 7-2User2.
- page E7-478Type the following in cells A20:D20:
- Ladies Parka LP10Blue 4 87
- Save and close the workbook.
- Open [your initials] Excel 7-2User1 and select cell A21.
- In cells A21:D21, type the following:
- Men’s Parka MP16Black 4 98
- Save the workbook and leave it open.
- Merge copies of the shared workbook.
- Click the Compare and Merge Workbooks button. (Save the file if prompted.)
- Navigate to the folder with [your initials] Excel 7-2User2 in the Select Files to Merge Into Current Workbook dialog box.
- Select [your initials] Excel 7-2User2 and click OK.
- Accept or reject changes.
- Click the Track Changes button [Review tab, Changes group] and choose Accept/Reject Changes.
- Verify that Not yet reviewed displays as the When option.
- Click OK. The first edit is cell A21.
- Click Accept to locate the next edit.
- Click Accept All and then press Ctrl+Home.
- Save the workbook as [your initials] Excel 7-2Merged in your folder and close it
- Move the template from the default folder.
- Click the File tab, select Open, and click Browse.
- Expand the Quick access list in the left pane and select Documents.
- Double-click Custom Office Templates.
- Right-click [your initials] Excel 7-2Template and choose Cut.
- In the left pane, navigate to and select the folder for saving files.
- Right-click an unused area of the dialog box and choose Paste.
- Click Cancel to close the dialog box.
- Remove the Compare and Merge Workbooks button and the Developer tab.
- Select the Options command [File tab] and click Quick Access Toolbar in the left pane.
- Click Compare and Merge Workbooks in the list on the right.
- Click Remove.
- Click Customize Ribbon in the left pane.
- Deselect the Developer box in the Main Tabs group and click OK.