Guided Project 7-2


Guided Project 7-2

In this project, you complete a vendor order template for Blue Lake Sporting Goods by adding data validation, formulas, and form controls. You protect the sheet and the structure and save the workbook as a template.

[Student Learning Outcomes 7.1, 7.2, 7.3, 7.4, 7.5, 7.6]

File Needed: BlueLake-07.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Names: [your initials] Excel 7-2Template.xltx and [your initials] Excel 7-2.xlsx

Skills Covered in This Project

  • Set validation criteria to use a list.
  • Use an error alert for data validation.
  • Create an input message without validation criteria.
  • Insert a check box form control.
  • Allow edit ranges in a worksheet.
  • Protect a worksheet.
  • Protect workbook structure.
  • Save a workbook as a template.
  • Create a new workbook from a template.
  • Inspect a workbook.
  • Check compatibility.
  1. Open the BlueLake-07 workbook from your student data files.
  2. Save the workbook as a template.
    1. Click the Save As button [File tab] and choose This PC.
    2. Click the More Options link.
    3. Type the file name [your initials] Excel 7-2Template in the File name box.
    4. Verify that the Save Thumbnail box is selected or select it.
    5. Choose Excel Template from the Save as type drop-down box.
    6. Confirm the Custom Office Templates folder and click Save.
  3. Set validation criteria to use a list and display an error alert.
    1. Click the Vendor Order sheet tab and select B17.
    2. Click the Data Validation button [Data tab, Data Tools group].
    3. Click the Allow arrow and choose List on the Settings tab.
    4. Click the Source entry box.
    5. Click the Departments sheet tab and select cells A2:A15.
    6. Click the Error Alert tab.
    7. Verify that the Show error alert after invalid data is entered box is selected.
    8. Use Stop for the Style to prohibit an invalid entry.
    9. Click the Title box and type Wait!.
    10. Click the Error message box and type Please choose from the list. including the period.
    11. Click OK.
  4. Add an input message without validation criteria.
    1. Select B11. You will enter a message to guide the user in entering the vendor’s information.
    2. Click the Data Validation button [Data tab, Data Tools group].
    3. Verify or select Any value for the Allow choice on the Settings tab.
    4. Click the Input Message tab.
    5. Verify that the Show input message when cell is selected box is selected.
    6. Click the Title box and type Vendor List.
    7. Click the Input message box and type Refer to the current vendor workbook. including the period.
    8. Click OK. The message displays when cell B11 is selected (Figure 7-72).
    9. Select cell B12.
  5. Display the Developer tab.
    1. Select the Options command [File tab] and click Customize Ribbon in the left pane.
    2. Select the Developer box in the Main Tabs group.
    3. Click OK.
  6. Insert a check box form control.
    1. Click the View tab and check the Gridlines box in the Show group so that you can draw and position controls with gridlines.
    2. Click the Insert Controls button [Developer tab, Controls group] and click the Check Box (Form Control) button.
    3. Draw a control directly over cell B15.
    4. Click the Properties button [Developer tab, Controls group].
    5. Verify that the Unchecked radio button is selected on the Control tab.
    6. Select the 3-D shading box.
    7. Click the Protection tab and deselect the Locked box.
    8. Click OK.
    9. Click near the word “Check” in the control to place an insertion point.
    10. Delete Check Box n, press Spacebar, and type Yes (Figure 7-73).
    11. Click cell E15 to deselect the control. If you accidentally placed a check mark within the control, point and click to remove it.
  7. Copy a check box form control.
    1. Point to the check box control label Yes and right-click.
    2. Choose Copy from the menu and then press Ctrl+V to paste the control.
    3. Point to any border of the copied control to display a move pointer.
    4. Drag the copy to cell C15.
    5. Press Ctrl and right-click while pointing to the first check box control to select both controls.
    6. Click the Align button [Shape Format tab, Arrange group] and choose Align Middle.
    7. Select cell E15 to deselect both controls.
    8. Right-click the copied control and choose Edit Text to place an insertion point.
    9. Delete Yes and type No (Figure 7-74).
    10. Select cell E15. Remove check marks that you accidentally entered.
  8. Complete formulas for the template.
    1. Select E30.
    2. Create a formula to multiply the result in cell E29 by 10%.
    3. Select cell E31 and create a formula to multiply the result in cell E29 by 8%.
  9. Allow edit ranges in the worksheet.
    1. Select D7.
    2. Press Ctrl and select cell D9, cells B11:D13, cell B17, and cells A20:D28.
    3. Click the Allow Edit Ranges button [Review tab, Protect group].
    4. Click New to open the New Range dialog box.
    5. Type OrderForm as the Title (Figure 7-75).
    6. Click the Range password box and type 123.
    7. Click OK, retype the password, and click OK.
    8. Click Apply in the Allow Users to Edit Ranges dialog box.
    9. Click OK to close the dialog box.
  10. Protect the worksheet and the workbook structure.
    1. Select D7.
    2. Click the Protect Sheet button [Review tab, Protect group].
    3. Allow the options to select locked and unlocked cells.
    4. Do not use a password and click OK.
    5. Click the Protect Workbook button [Review tab, Protect group].
    6. Verify that the Structure box is selected.
    7. Do not use a password and click OK.
  11. Save and close the template.
  12. Create a workbook from a template.
    1. Click the New button [File tab] and click Personal.
    2. Click [your initials] Excel 7-2Template to create a workbook.
    3. Select cell D7.
    4. Type BLS00120 in cell D7 and press Enter.
    5. Type =to in cell D9, press Tab to select TODAY, and press Enter.
    6. Type the following in cells B11:B13:
      • Outdoor Apparel, Inc.
      • 4232 South Water Street
      • Omaha, NE 68107
    7. Select the check box control for Yes.
    8. Select cell B17, click the data validation arrow, and choose Apparel.
    9. Type the following in cells A20:D21:Lavender ParkaLP10487Midnight ParkaMP16498
  13. Save the workbook as an Excel workbook named [your initials] Excel 7-2 in your usual location for saving files.
  14. Remove sheet protection and inspect the workbook.
    1. Select the Info command on the File tab. You cannot inspect a worksheet that is protected.
    2. Click Unprotect in the Protect Workbook area. Workbook structure is still protected.
    3. Click the Check for Issues button and choose Inspect Document.
    4. Choose Yes to save the document before inspection.
    5. Click Inspect in the Document Inspector dialog box.
    6. Click Remove All to remove document properties and personal information.
    7. Click Close.
  15. Check compatibility.
    1. Click the Protect Workbook button [Review group, Protect group]. The Protect Workbook property must be disabled to generate a compatibility report on a new sheet.
    2. Click the Check for Issues button [File tab, Info group] and choose Check Compatibility.
    3. Click Copy to New Sheet. The compatibility report displays on a new sheet. Your report will be similar to but may not match the figure.
    4. Click the Vendor Order sheet tab.
  16. Save and close the workbook (Figure 7-76).
  17. Move the template from the default folder.
    1. Click the File tab, select Open, and click Browse to display the Open dialog box.
    2. Expand the Quick access list if necessary and choose the Documents folder in the left pane.
    3. Double-click Custom Office Templates in the pane on the right.
    4. Right-click [your initials] Excel 7-2Template and choose Cut.
    5. Use the left pane to navigate to and open your folder for saving files.
    6. Right-click an unused area of the dialog box and choose Paste.
    7. Click Cancel to close the dialog box.
  18. Hide the Developer tab.
    1. Select the Options command [File tab].
    2. Click Customize Ribbon in the left pane.
    3. Deselect the Developer box in the Main Tabs group and click OK.

Do you need help with this assignment or any other? We got you! Place your order and leave the rest to our experts.

Place Your Order

Quality Guaranteed

Any Deadline

No Plagiarism