question archive New Perspectives Excel 2016 | Module 7: SAM Project 1a Reclaim the River Developing an Excel Application GETTING STARTED Open the file NP_EX16_7a_FirstLastName_1
Subject:MS ExcelPrice: Bought3
New Perspectives Excel 2016 | Module 7: SAM Project 1a
Reclaim the River
Developing an Excel Application
GETTING STARTED
Open the file NP_EX16_7a_FirstLastName_1.xlsm, available for download from the SAM website.
Save the file as NP_EX16_7a_FirstLastName_2.xlsm by changing the “1” to a “2”.
0. If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
With the file NP_EX16_7a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
· If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
If you see a Message Bar with a security warning at top of the Excel window, click the Enable Content button in the Message Bar to enable the macros contained in the file (see Figure 1).
Figure 1: Security Warning Message Bar
To complete this project you will need to display the Developer tab. To add this tab to the Excel ribbon, click the File tab to open Backstage View and then click the Options button. In the Excel Options dialog box, click the Customize Ribbon option and click the Developer check box (see Figure 2). Click the OK button to close the Excel Options dialog box and confirm the Developer tab appears in the Excel Ribbon.
Figure 2: Customize Ribbon Section of Excel Options Dialog Box
Dee Amytha is the coordinator for the Reclaim the River, a 3-day volunteer event where local businesses and community groups help to revitalize the banks of the Charles River. She has asked for your help updating the worksheet she is using to track and record volunteer information.
Switch to the Volunteer Totals worksheet, and then unprotect it.
Dee started to create named ranges in the worksheet and has asked you to finish the updates.
Create a defined name for the range C5:C10, using Friday_Afternoon as the range name.
View the comment associated with cell A10. Follow the instructions in the comment to update a value in the worksheet, and then delete the comment.
Edit the defined name associated with the range C15:C20 to be Saturday_Afternoon instead of Sat_Aftr. [Mac Hint: Delete the named range Sat_Aftr, and use Saturday_Afternoon as the a new named range for the range C15:C20.]
Apply the defined names Saturday_Morning, Sunday_Morning, and Sunday_Afternoon to the existing formulas on the Volunteer Totals worksheet. Ignore Relative/Absolute value and use row and column names. (Hint: If you receive an error that Excel cannot find any references to replace, make sure that only a single cell, rather than a range, in the worksheet is selected prior to applying the defined names.)
Select the range G3:H8 and create names from the selection, using the values shown in the Left column.
In cell H9, enter a formula using the SUM function to total the values in the defined range Total_Volunteers. Use the defined range Total_Volunteers in your formula.
a. Change the column width of columns A:D to 21.00 characters.
b. Merge and center the range A1:D1.
c. Enter the text Reclaim the River into the merged range A1:D1.
d. Italicize the merged range A1:D1 and apply a 24 pt. font size to the merged range.
f. Click the Stop Recording button in the Code group on the Developer tab.
Switch to the Volunteer Registration worksheet.
Delete the defined name Volunteer_Data associated with the range C4:C8 on this worksheet.
Assign the Clear_Data macro to the Clear Data macro button in the range B10:B11.
Edit the Clear_Data macro (found in Module 1).
g. Assign the macro Record_Volunteer to the button.
h. Change the button label using Record Volunteer as the new name.
i. The dates should be between 5/18/2018 and 5/20/2018.
l. The validation rule should ignore blanks and appear as an in-cell dropdown.
Edit the data validation rule associated with cell C8 as described below:
Enter the following volunteer information into the worksheet:
r. In cell C4, enter Kate Bryant as the Volunteer Name.
s. In cell C5, enter Ultimate Craft Center as the Organization.
t. In cell C6, enter 5/19/2018 as the Volunteer Date.
u. In cell C7, select River Cleanup as the Assignment.
v. In cell C8, select S as the T-Shirt Size.
Protect the structure of the current workbook without using a password.
Final Figure 1: Volunteer Totals Worksheet
Final Figure 2: Volunteer Letterhead Worksheet