Homework answers / question archive / New Perspectives Excel 2013 Tutorial 7: SAM Project 1a Mike Lalicich 1

New Perspectives Excel 2013 Tutorial 7: SAM Project 1a Mike Lalicich 1

MS Excel

Share With

---

New Perspectives Excel 2013 Tutorial 7: SAM Project 1a

Mike Lalicich

1. Go to the Consolidated worksheet and unprotect the worksheet.

2. 

Create the defined name for the following ranges:

a. Use the defined name Groupware_Subscriptions for the range C6:E6.

b. Use the defined name Groupware_Licenses for the range C7:E7.

c. Use the defined name Groupware_Other for the range C8:E8.

3. 

Delete the defined name Trends assigned to the range G5:G21.

4. 

Edit the defined name ConsultingRevenueTotals (which represents the

range C20:E20), so that the name is shortened to Consulting.

5. 

Apply the defined names SMail_Licenses, SMail_Other, and

SMail_Subscriptions to existing formulas in the Consolidated Worksheet.

(Hint: Don’t change any of the default options in the Apply Name dialog

6. 

Enter formulas using the SUM function and defined names in the range

F16:F18 as described below:

a. In cell F16, use the SUM function to total values in the defined range

MathGenius_Subscriptions.

b. In cell F17, use the SUM function to total the values in the defined

range MathGenius_Licenses.

c. In cell F18, use the SUM function to total the values in the defined

range MathGenius_Other.

7. 

Insert the following comment in cell B13: Other sales include individual

downloads and box sales. (including the period.) Remove any pre-

existing text in the comment. Hide the comment.

8. 

Edit the comment in cell B16 by changing 2015 to 2016. The comment

should read as follows: Version2 release delayed. Most Version1

subscriptions and licenses expired in 2016; contracts expected to renew

in 2017 with Version2 release. Hide the comment.

9. 

Create a macro button in cell G3, directly below the View Sparklines

button and then complete the following actions:

a. Assign the macro HideSparklines to the button.

b. Change the button label to Hide Sparklines.

c. Run the HideSparklines macro.

10. 

Make sure the Use Relative References option is not selected in the

Developer tab. Then record a macro named ViewSparklines stored in the

current workbook with the description Displays sparkline charts for each

row of product revenues and the keyboard shortcut ctrl + t. With the

macro recording, complete the following actions:

a. Select the range G6:G22.

b. On the ribbon, click the INSERT tab.

c. In the Sparklines group, click the Line button.

d. In the Create Sparklines dialog box, enter the range C6:E22 in the

Data Range box. (Hint: The Location Range box should already contain

the range G6:G22.)

e. Click the OK button.

f. Click the Stop Recording button in the Code group on the DEVELOPER

tab.

g. Assign the ViewSparklines macro to the View Sparklines button in cell

G2.

Confirm your macro works as expected by switching to the Manchester

worksheet and running the ViewSparklines macro you just recorded.

(Hint: Use the keyboard shortcut ctrl + t to run your macro.)

11. 

Go to the Sales Data Entry Form worksheet. Unlock the cells in the range

C5:C11.

12. 

Select the range B5:C10 and create defined names for the range using

the Create from Selection option. The names will be located in the left

column in this range.

13. 

Assign the Clear macro to the Clear macro button in cell C11.

14. 

Edit the Clear macro.

In the Clear macro VBA code, change the range used in the code to the

range C5:C10. The final line of code should read: Range("C5:C10").Select

(see Figure 3 for details)

Run the Clear macro to confirm that the macro erases the value in cell

C10.

15. 

Create a validation rule for cell C5 that will only allow values from a list.

The validation rule should have the following options:

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

dropdown.

b. For the data validation source, enter the following list: Spring

Groupware, Spring Mail, Spring Math Genius, Spring Consulting.

c. The input message should have the title Product Name and the Input

message Click the arrow to select a product. (including the period.)

d. The error alert should have the Stop style with the title Invalid

Product and the error message An invalid product has been entered.

(including the period.)

16. 

Create a data validation rule in cell C8 specifying that all inputs must be

dates with the following options:

a. The dates should be greater than or equal to 1/1/2014.

b. The input message should have the title Date of Sale and the Input

message Enter Date of Sale. (including the period.)

c. The error alert should have the stop style with the title Invalid Date of

Sale and the error message Date of Sale must be after 1/1/2014.

(including the period.)

17. 

Enter the following data for a customer order:

a. In cell C5, select Spring Mail as the Product Name.

b. In cell C6, enter Springfield Elementary as the Customer Name.

c. In cell C7, enter Subscription as the Contract Type.

d. In cell C8, enter the date 7/26/2012 as the Date of Sale. When the

error message appears, click cancel and enter the valid date of sale

7/26/2014

e. In cell C9, enter the value $50,000 as the Total Sale amount.

f. Do not enter a value in cell C10.

18. 

Protect the Sales Data Entry Form worksheet. Do not use a password.

19. 

Protect the structure of the current workbook. Do not use a password.