19 EASY STEPS of EXCEL HW NEEDED!!

Description is the STEPS of this HW.

HW itself is the attachment (the excel) 

PROJECT DESCRIPTION

Sophie Campbell works in the Sales Department at Spring Software, a software development company headquartered in Cambridge, Massachusetts. She has created a workbook to track sales of Spring Software’s various product lines, and she wants you to create macros that make the workbook easier to use. Then, she would like you to protect the workbook to prevent unauthorized users from inadvertently making changes.

GETTING STARTED

·       Download the following file from the SAM website:

o   NP_Excel2013_T7_P1a_FirstLastName_1.xlsm

o   This file is has .xlsm extension, indicating it is an Excel Macro-Enabled Workbook.

·       Open the file you just downloaded and save it with the name:

o   NP_Excel2013_T7_P1a_FirstLastName_2.xlsm

o   Hint: If you do not see the .xlsm file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

·       With the file NP_Excel2013_T7_P1a_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. (refer to image below)

Figure 1: Security Warning Message Bar

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

·       To complete this project you will need to display the Developer tab. To add this tab to the Excel ribbon, click on the File tab to open Backstage view and then click the Options button. In the Excel Options dialog box, click on 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

PROJECT STEPS

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

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.

Figure 3: Clear Macro VBA code

Copyright © 2014 Cengage Learning. All Rights Reserved.

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.

Your workbook should look like the Final Figures on the following pages. Save your changes, remembering to save the workbook as Macro-enabled with the .xlsm file extension. Close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.