Homework answers / question archive / New Perspectives Excel 2016 Module 7: SAM Project 1a SHADMAN JOHAR 1

New Perspectives Excel 2016 Module 7: SAM Project 1a SHADMAN JOHAR 1

MS Excel

Share With

---

New Perspectives Excel 2016 Module 7: SAM Project 1a

SHADMAN JOHAR

1. 

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.

2. 

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.

3. 

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.

4. 

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.]

5. 

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.)

6. Select the range G3:H8 and create names from the selection, using the values shown in the

Left column.

7. 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.

8. 

Insert the following comment into the merged range G1:H1: Consider moving this table to

another worksheet. (including the period). Then, remove any pre-existing text in the

comment, and hide the comment.

9. 

Dee wants to create a macro that will allow her to quickly add the Reclaim the River header

to any worksheet she adds to the workbook.

Switch to the Volunteer Letterhead worksheet. Make sure the Use Relative References option

is not selected in the Developer tab. [Mac Hint: The Developer pane doesn't include this

option, so Mac users can ignore this option.]

Record a macro named RtR_Letterhead, stored in the current workbook with the keyboard

shortcut Ctrl+ Shift+L [Mac Hint: Option+Cmd+Shift+L] and the description Macro to add the

Reclaim the River letterhead to a worksheet. (including the period). With the macro

recording, complete the following actions:

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.

e. Change the background color to Blue, Accent 5, Lighter 40% (9th column, 4th row in the

Theme Colors palette) and the font color to White, Background 1 (1st column, 1st row in the

Theme Colors palette).

f. Click the Stop Recording button in the Code group on the Developer tab.

Confirm that your macro works as expected by switching to the Volunteer Records worksheet

and running the RtR_Letterhead macro you just recorded. (Hint: You can use the keyboard

shortcut you just created to run your macro.)

10. 

Switch to the Volunteer Registration worksheet.

Delete the defined name Volunteer_Data associated with the range C4:C8 on this worksheet.

11. 

Dee created two macros to use on the Volunteer Registration worksheet. She wants you to

review the macros and assign them to buttons, to make them easier to access.

Assign the Clear_Data macro to the Clear Data macro button in the range B10:B11.

12. 

Edit the Clear_Data macro (found in Module 1).

In the Clear_Data macro VBA code, change the range used in the code to the range C4:C8.

The edited code should read: Range("C4:C8").Select (see Figure 3 for details).

Run the Clear_Data macro to confirm that the macro erases the values in the range C4:C8.

(Hint: You must complete Step 17 correctly to receive credit.)

13. 

Create a form control macro button in the range C10:C11, next to the Clear Data button and

then complete the following actions:

a. Assign the macro Record_Volunteer to the button.

b. Change the button label using Record Volunteer as the new name.

14. 

In cell C6, create a data validation rule that will only accept date values corresponding to the

dates of the Reclaim the River event as described below:

a. The dates should be between 5/18/2018 and 5/20/2018.

b. The input message should have the title Volunteer Date and should use the text Enter

volunteer date. (including the period) as the Input message.

c. The error alert should have the Stop style with Invalid volunteer date. (including the

period) as the title.

15. 

In cell C7, create a data validation rule that will only accept values from a list corresponding

to the different assignment at the Reclaim by the River event as described below:

a. The validation rule should ignore blanks and appear as an in-cell dropdown.

b. Enter the following list: Waterfront Cleanup, River Cleanup, Landscaping, Material

Transport, Refreshments, Support as the validation source.

c. The input message should have the title Assignment and should use Select an assignment

from the list. (including the period) as the Input message.

d. The error alert should have the Stop style with Invalid assignment. (including the period)

as the title.

16. 

Edit the data validation rule associated with cell C8 as described below:

a. The input message should have the title T-Shirt Size and should use Select a t-shirt size

from the list. (including the period) as the Input message.

b. The error alert should have the Stop style with Select a valid t-shirt size. (including the

period) as the title.

17. 

Enter the following volunteer information into the worksheet:

a. In cell C4, enter Kate Bryant as the Volunteer Name.

b. In cell C5, enter Ultimate Craft Center as the Organization.

c. In cell C6, enter 5/19/2018 as the Volunteer Date.

d. In cell C7, select River Cleanup as the Assignment.

e. In cell C8, select S as the T-Shirt Size.

18. 

With all the changes that you've made to the worksheet, Dee now wants to make sure that

no further changes can be made.

Format the range C4:C8, so that the cells in this range are unlocked. Then protect the

Volunteer Registration worksheet without using a password and using the default settings.

 

19. 

Protect the structure of the current workbook without using a password.