New Perspectives Excel 2019 | Module 5: SAM Project 1b | Min Jee Woo Artwork

Min Jee Woo Artwork
GENERATING REPORTS FROM MULTIPLE WORKBOOKS

GETTING STARTED
• Open the file NP_EX19_5b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_5b_FirstLastName_2.xlsx by changing the “1” to a “2”.
o If you do not see the .xlsx 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_5b_Combined_2020.xlsx
o Support_EX19_5b_Descriptions.docx
• With the file NP_EX19_5b_FirstLastName_2.xlsx 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.
• When opening your start file or the Graded Summary Report, you may be prompted to update the links. Select Don't Update in the dialog box.
PROJECT STEPS
1. Min Jee Woo is an artist who makes her living selling her artwork at art fairs around the Midwest. Min Jee is using an Excel workbook with multiple worksheets to summarize the sales of her artwork by fair location. She asks for your help in completing the sales information for this year. Break the external link in the worksheet, so that the formulas in the range B3:D6 of the Art Fairs worksheet are replaced with static values. Then switch to the Art Fairs worksheet.
2. In cell B5, remove the hyperlink, leaving the unlinked text "Eva Martinez" in the cell.
3. In cell D4, create a hyperlink to an email address as follows:
a. Link to the following email address: info@gateway.example.com
b. Use info@gateway.example.com as the text to display.
c. Use Contact the Gateway Art Fair organizers as the ScreenTip text.
4. In cell B10, create a hyperlink to a document describing the four art fairs as follows:
a. Link to the file Support_NP_EX19_5b_Descriptions.docx.
b. Use Art fair descriptions as the text to display.
c. Use Descriptions of the four art fairs as the ScreenTip text.
5. Edit the hyperlink in cell B9 as follows:
a. Use Online art fair calendar as the display text.
b. Use Display the calendar of art fairs in the U.S. as the ScreenTip text.
6. Min Jee wants to apply consistent formatting to the worksheets she collected from separate workbooks. Group the Chicago, St. Louis, and Minneapolis worksheets together and then make the following formatting updates:
a. Change the font size in the merged range A1:F1 to 16 point.
b. Apply the 20% - Accent 1 cell style to the merged range A2:F2.
c. Bold the values in the range A5:A8.
d. Apply the Accounting number format with two decimal places and $ as the symbol to the range B5:F9.
e. Resize the column width of columns B:F to 14. Do not ungroup the worksheets.
7. With the Chicago, St. Louis, and Minneapolis worksheets still grouped, update the worksheet as follows:
a. In cell A8, change the text "Marble" to read:
Stone
b. In cell A9, change the text "Total" to read:
Total sales
Do not ungroup the worksheets.
8. With the Chicago, St. Louis, and Minneapolis worksheets still grouped, create a formula as follows:
a. Enter a formula in cell B9 using the SUM function that totals the sales for Q1.
b. Copy the formula to the range C9:E9.
Ungroup the worksheets and then check to confirm that the formatting and formulas from Steps 6-8 are present in all three worksheets.
9. Min Jee wants to create a copy of the formatted Minneapolis worksheet to use for sales data from the upcoming Madison art fair. Create a copy of the Minneapolis worksheet between the Minneapolis worksheet and the Combined Sales worksheet, and then update the worksheet as follows:
a. Change the worksheet name to Madison for the copied worksheet.
b. Edit the text to read Madison 2021 in the merged range A2:F2.
c. Clear the contents of the range B5:E8.
10. Min Jee wants to combine the sales data from each of the art fairs. Switch to the Combined Sales worksheet, and then update the worksheet as follows:
a. In cell A5, enter a formula without using a function that references cell A5 in the Madison worksheet.
b. Copy the formula from cell A5 to the range A6:A8 without copying the formatting.
c. In cell B5, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell B5 in the Chicago:Madison worksheets.
d. Copy the formula from cell B5 to the range B6:B8 without copying the formatting.
e. Copy the formulas and the formatting from the range B5:B8 to the range C5:E8. (Hint: You can ignore the error about empty cells because Min Jee will enter the Madison sales data later.)
11. Min Jee started to create named ranges in the worksheet and has asked you to complete the work. Create a defined name for the range B5:E5 using Ceramics as the range name.
12. Create names from the range A6:E8 using the values shown in the left column.
13. Apply the defined names Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales to the formulas in the range B9:E9.
14. Change the defined name to Total_Sales_2021 for the range F5:F8. [Mac Hint: Delete the existing defined name "Totals" and add the new defined name.]
15. Min Jee wants to compare 2021 sales totals to the sales totals for 2020 and needs to add the 2020 data to the Combined Sales worksheet. Open the file Support_EX19_5b_Combined_2020.xlsx. Switch back to the original workbook and go to the Combined Sales worksheet. Create external references as follows:
a. Link cell G5 in the Combined Sales worksheet to cell F5 in the Combined Sales worksheet in the Support_EX19_5b_Combined_2020.xlsx workbook.
b. Link cell G6 in the Combined Sales worksheet to cell F6 in the Combined Sales worksheet in the Support_EX19_5b_Combined_2020.xlsx workbook.
c. Link cell G7 in the Combined Sales worksheet to cell F7 in the Combined Sales worksheet in the Support_EX19_5b_Combined_2020.xlsx workbook.
d. Link cell G8 in the Combined Sales worksheet to cell F8 in the Combined Sales worksheet in the Support_EX19_5b_Combined_2020.xlsx workbook.
e. Do not break the links. Close the Support_EX19_5b_Combined_2020.xlsx workbook.
16. In cell G9, enter a formula to total the values in the defined range Totals_2020, using the SUM function and the defined range name.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
 
Final Figure 1: Art Fairs Worksheet

Final Figure 2: Chicago Worksheet

Final Figure 3: St. Louis Worksheet

Final Figure 4: Minneapolis Worksheet

Final Figure 5: Madison Worksheet

Final Figure 6: Combined Sales Worksheet