GETTING STARTED

·   Open the file NP_EX19_CS9-12a_FirstLastName_1.xlsm, available for download from the SAM website.

·   Save the file as NP_EX19_CS9-12a_FirstLastName_2.xlsm by changing the “1” to a “2”.

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

·   To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

o  Support_EX19_CS9-12a_Employees.accdb

o  Support_EX19_CS9-12a_Sales.csv

·   With the file NP_EX19_CS9-12a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

o  If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

     PROJECT STEPS

1. Hwan Sung is a financial analyst at W&K Engineering in Bismarck, North Dakota. He is using an Excel workbook to analyze the financial data for engineering projects, recent revenue, and funding for additional construction equipment. He asks for your help in analyzing data, making financial calculations, and correcting errors.
Go to the Projects worksheet and then unprotect it so you can edit the contents.

2. Hwan created a macro named CopyStats to copy the project statistics in the range I3:L22, and then paste them on a new worksheet. When he runs the CopyStats macro, however, it includes extra data from column G. View and edit the macro as follows:

a. Open the CopyStats macro in the Visual Basic Editor. The macro should select and copy the range I3:L22.

b.  In the CopyStats macro VBA code, change the range specified in the first line of code after the comments (Range("G3:L22").Select) to use I3:L22 as the range.

c.  Save and close the CopyStats macro.

d. Run the CopyStats macro.

e. Use Project Stats as the name of the new worksheet the macro created.

3. Return to the Projects worksheet. Hwan also created a macro named AddRow that inserts a new blank row in the Projects table in the range A4:G37. He wants to assign the AddRow macro to a new button to make running the macro and adding a new project easy. Create the button as follows:

a. Insert a Button (Form Control) button in cell I1 to the left of the Print Stats button.

b. Assign the AddRow macro to the new button.

c. Change the height of the new button to 0.3" and the width to 1".

d. Align the new button to the left side of cell I1, and then align its top and bottom with the top and bottom of the Print Stats button.

e. Edit the text to display Add Project on the new button.

4. In the Projects table, Hwan wants to ensure that everyone entering project information enters the correct project categories listed in the range I4:I7. He asks you to ensure users enter a value from this range. Create a data validation rule as follows:

a. In the range C4:C37, create a data validation rule that accepts only List values from the range I4:I7.

b. Create an input message that uses Project Category as the title and the following text as the input message:
Enter a project category.

c. Create a Stop style error alert that uses Invalid Category as the title and the following text as the error message:
Enter a category listed in column I.

5. Hwan wants to make sure all of the categories entered in the Category column of the Projects table are valid. Identify and correct any invalid values as follows:

a. In the range C4:C37, circle invalid data.

b. Change the invalid entry to Site development to correct the error.

6. In the range I3:L7, Hwan wants to summarize project information. Calculate the number of projects, the total estimates, and the average estimate in each category as follows:

a. In cell J4, enter a formula using the COUNTIF function that counts the number of projects in the Bridge category, checking that the column of categories in the Projects table (Projects[Category]) is equal to the value in cell I4.

b. Fill the range J5:J7 with the formula in cell J4, filling without formatting.

c. In cell K4, enter a formula using the SUMIF function that totals the estimates for projects in the Bridge category, checking that the column of categories in the Projects table (Projects[Category]) is equal to the value in cell I4, and that the formula totals the values in the Estimate column (Projects[Estimate]).

d. Fill the range K5:K7 with the formula in cell K4, filling without formatting.

e. In cell L4, enter a formula using the AVERAGEIF function that averages the estimates of projects in the Bridge category, checking that the column of categories in the Projects table (Projects[Category]) is equal to the value in cell I4, and that the formula averages the values in the Estimate column (Projects[Estimate]).

f.  Fill the range L5:L7 with the formula in cell L4, filling without formatting.