Module 5: SAM Project 1b
Ash17luvz
NP_EX365_2021_5b_AshleyMedina_Report_1.xlsxGraded Summary Report
| ashley medina | |||
| SUBMISSION #1 | SCORE IS: 54 OUT OF 100 | GE ver. 17.2.0-rc0000 | ||
| 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. | 6/6 | |
| Break links to an external workbook. | |||
| 2. | In cell B5, remove the hyperlink, leaving the unlinked text "Eva Martinez" in the cell. | 6/6 | |
| Remove a hyperlink. | |||
| 3. | In cell D4, create a hyperlink to an email address as follows: a. Link to the following email address: [email protected] b. Use [email protected] as the text to display. c. Use Contact the Gateway Art Fair organizers as the ScreenTip text. | 2/6 | |
| Create a hyperlink to an email address. | |||
| In the Art Fairs worksheet, cell D4 should contain a hyperlink to the email address [email protected]. | |||
| Set the display text as a hyperlink. | |||
| In the Art Fairs worksheet, the hyperlink in cell D4 should display the text "[email protected]". | |||
| Set the ScreenTip for a hyperlink. | |||
| 4. | In cell B10, create a hyperlink to a document describing the four art fairs as follows: a. Link to the file Support_EX365_2021_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. | 6/6 | |
| Add a hyperlink to an external workbook. | |||
| Set the display text for a hyperlink. | |||
| Set the ScreenTip for a hyperlink. | |||
| 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. | 0/6 | |
| Set the display text for a hyperlink. | |||
| In the Art Fairs worksheet, the hyperlink in cell B9 should display the text "Online art fair calendar". | |||
| Set the ScreenTip for a hyperlink. | |||
| In the Art Fairs worksheet, the hyperlink in cell B9 should display the ScreenTip text "Display the calendar of art fairs in the U.S." | |||
| 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. | 5/6 | |
| Change the font size. | |||
| Apply a cell style. | |||
| Change the font style. | |||
| Change the number format. | |||
| The merged range B5:F9 in the Chicago, St. Louis, and Minneapolis worksheets should be formatted using the Accounting number format. | |||
| Change the column width. | |||
| 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. | 6/6 | |
| Update a value in a cell. | |||
| Update a value in a cell. | |||
| 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. | 6/6 | |
| Create a formula using a function. | |||
| Copy a formula into a range. | |||
| 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 2024 in the merged range A2:F2. c. Clear the contents of the range B5:E8. | 6/6 | |
| Copy a worksheet to a specific location. | |||
| Change the name of a worksheet. | |||
| Update a value in a cell. | |||
| Clear cell contents. | |||
| 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.) | 4/6 | |
| Create a formula without using a function. | |||
| Copy a formula into a range without formatting. | |||
| In the Combined Sales worksheet, cell A5 contains an incorrect formula or formatting. | |||
| Create a formula using a function. | |||
| Copy a formula into a range. | |||
| In the Combined Sales worksheet, the formatting from cell B5 should not be copied to the range B6:B8. | |||
| Copy a formula into a range. | |||
| 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. | 0/6 | |
| Create defined names for a range. | |||
| In the Combined Sales worksheet, the range B5:E5 should have the defined name "Ceramics". | |||
| 12. | Create names from the range A6:E8 using the values shown in the left column. | 0/6 | |
| Create defined names for a range. | |||
| In the Combined Sales worksheet, the names for the range A6:E8 have not been created correctly. | |||
| 13. | Apply the defined names Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales to the formulas in the range B9:E9. | 0/7 | |
| Use defined names in a formula. | |||
| In the Combined Sales worksheet, the defined names "Q1_Sales", "Q2_Sales", "Q3_Sales", and "Q4_Sales" have not been correctly applied to the range B9:E9. | |||
| 14. | Change the defined name to Total_Sales_2024 for the range F5:F8. [Mac Hint: Delete the existing defined name "Totals" and add the new defined name.] | 0/7 | |
| Change a defined name for a range. | |||
| In the Combined Sales worksheet, the range F6:F9 should have the defined name "Total_Sales_2024". | |||
| 15. | Min Jee wants to compare 2024 sales totals to the sales totals for 2023 and needs to add the 2023 data to the Combined Sales worksheet. Open the file Support_EX365_2021_5b_Combined_2023.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_EX365_2021_5b_Combined_2023.xlsx workbook. b. Link cell G6 in the Combined Sales worksheet to cell F6 in the Combined Sales worksheet in the Support_EX365_2021_5b_Combined_2023.xlsx workbook. c. Link cell G7 in the Combined Sales worksheet to cell F7 in the Combined Sales worksheet in the Support_EX365_2021_5b_Combined_2023.xlsx workbook. d. Link cell G8 in the Combined Sales worksheet to cell F8 in the Combined Sales worksheet in the Support_EX365_2021_5b_Combined_2023.xlsx workbook. e. Do not break the links. Close the Support_EX365_2021_5b_Combined_2023.xlsx workbook. | 0/7 | |
| Create a formula. | |||
| In the Combined Sales worksheet, the formula in cell G5 should be a reference to cell F5 in the Combined Sales worksheet in the Support_EX365_2021_5b_Combined_2023.xlsx workbook. | |||
| Create a formula. | |||
| In the Combined Sales worksheet, the formula in cell G6 should be a reference to cell F6 in the Combined Sales worksheet in the Support_EX365_2021_5b_Combined_2023.xlsx workbook. | |||
| Create a formula. | |||
| In the Combined Sales worksheet, the formula in cell G7 should be a reference to cell F7 in the Combined Sales worksheet in the Support_EX365_2021_5b_Combined_2023.xlsx workbook. | |||
| Create a formula. | |||
| In the Combined Sales worksheet, the formula in cell G8 should be a reference to cell F8 in the Combined Sales worksheet in the Support_EX365_2021_5b_Combined_2023.xlsx workbook. | |||
| 16. | In cell G9, enter a formula to total the values in the defined range Totals_2023, using the SUM function and the defined range name. | 7/7 | |
| Create a formula using a function. |
Documentation
| Min Jee Woo Artwork | |
| GENERATING REPORTS FROM MULTIPLE WORKBOOKS | |
| Author: | ashley medina |
| Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. | |
Art Fairs
| Art Fair Contacts | |||
| Location | Director | Venue | |
| Chicago, IL | Natalia Reyes | Illinois Center Pavilion | [email protected] |
| St. Louis, MO | Owen McKay | Gateway Park | http://[email protected]
Grading Engine: Grading Error: Step 3: In the Art Fairs worksheet, cell D4 should contain a hyperlink to the email address [email protected]. Step 3: In the Art Fairs worksheet, the hyperlink in cell D4 should display the text "[email protected]". |
| Minneapolis, MN | Eva Martinez | Twin Cities Promenade | [email protected] |
| Madison, WI | Will Trainor | Capitol Square | [email protected] |
| Online art fair calender
Grading Engine: Grading Error: Step 5: In the Art Fairs worksheet, the hyperlink in cell B9 should display the text "Online art fair calendar". Step 5: In the Art Fairs worksheet, the hyperlink in cell B9 should display the ScreenTip text "Display the calendar of art fairs in the U.S." |
|||
| Art Fair Descriptions |
Chicago
| Art Fairs | |||||
| Chicago 2024 | |||||
| Q1 | Q2 | Q3 | Q4 | Total | |
| Ceramics | $ 612.50 | $ 724.35 | $ 1,115.80 | $ 845.00 | $ 3,297.65 |
| Fiber | $ 586.70 | $ 803.45 | $ 1,222.75 | $ 934.50 | $ 3,547.40 |
| Glass | $ 657.85 | $ 698.75 | $ 1,205.15 | $ 1,435.25 | $ 3,997.00 |
| Stone | $ 605.65 | $ 656.80 | $ 760.75 | $ 1,093.45 | $ 3,116.65 |
| Total sales | $ 2,462.70 | $ 2,883.35 | $ 4,304.45 | $ 4,308.20 | $ 13,958.70 |
St. Louis
| Art Fairs | |||||
| St. Louis 2024 | |||||
| Q1 | Q2 | Q3 | Q4 | Total | |
| Ceramics | $ 644.50
Grading Engine: Grading Error: Step 6: The merged range B5:F9 in the Chicago, St. Louis, and Minneapolis worksheets should be formatted using the Accounting number format. |
$733.30 | $1,105.50 | $825.25 | $3,308.55 |
| Fiber | $ 516.70 | $801.15 | $1,242.15 | $913.15 | $3,473.15 |
| Glass | $ 607.95 | $628.95 | $1,185.75 | $1,385.20 | $3,807.85 |
| Stone | $ 710.65 | $696.95 | $785.75 | $1,143.35 | $3,336.70 |
| Total sales | $2,479.80 | $2,860.35 | $4,319.15 | $4,266.95 | $13,926.25 |
Minneapolis
| Art Fairs | |||||
| Minneapolis 2024 | |||||
| Q1 | Q2 | Q3 | Q4 | Total | |
| Ceramics | $ 544.50 | $740.30 | $1,135.55 | $885.50 | $3,306 |
| Fiber | $ 516.40 | $781.15 | $1,278.15 | $933.75 | $3,509 |
| Glass | $ 707.05 | $658.15 | $1,155.50 | $1,385.20 | $3,906 |
| Stone | $ 725.65 | $762.95 | $725.25 | $943.65 | $3,158 |
| Total sales | $2,494 | $2,943 | $4,294 | $4,148 | $13,879 |
Madison
| Art Fairs | |||||
| Madison 2024 | |||||
| Q1 | Q2 | Q3 | Q4 | Total | |
| Ceramics | $0 | ||||
| Fiber | $0 | ||||
| Glass | $0 | ||||
| Stone | $0 | ||||
| Total sales | $0 | $0 | $0 | $0 | $0 |
Combined Sales
| Art Fairs | |||||||
| Combined Sales 2024 | |||||||
| Q1 | Q2 | Q3 | Q4 | Total | 2023 Total | ||
| Ceramics | $1,802 | $2,198 | $3,357 | $2,556 | $9,912 | $ 18,022.60 | |
| Fiber
Grading Engine: Grading Error: Step 10: In the Combined Sales worksheet, cell A5 contains an incorrect formula or formatting. |
$1,620
Grading Engine: Grading Error: Step 10: In the Combined Sales worksheet, the formatting from cell B5 should not be copied to the range B6:B8. |
$2,386 | $3,743 | $2,781 | $10,530 | $ 19,440.20 | |
| Glass
Grading Engine: Grading Error: Step 10: In the Combined Sales worksheet, cell A5 contains an incorrect formula or formatting. |
Grading Engine: Grading Error: Step 10: In the Combined Sales worksheet, the formatting from cell B5 should not be copied to the range B6:B8. |
$1,973
Grading Engine: Grading Error: Step 10: In the Combined Sales worksheet, the formatting from cell B5 should not be copied to the range B6:B8. |
$1,986 | $3,546 | $4,206 | $11,711 | $ 21,448.65 |
| Stone
Grading Engine: Grading Error: Step 10: In the Combined Sales worksheet, cell A5 contains an incorrect formula or formatting. |
Grading Engine: Grading Error: Step 10: In the Combined Sales worksheet, the formatting from cell B5 should not be copied to the range B6:B8. |
$2,042
Grading Engine: Grading Error: Step 10: In the Combined Sales worksheet, the formatting from cell B5 should not be copied to the range B6:B8. |
$2,117 | $2,272 | $3,180 | $9,611 | $ 17,179.75 |
| Total sales | $7,436
Grading Engine: Grading Error: Step 13: In the Combined Sales worksheet, the defined names "Q1_Sales", "Q2_Sales", "Q3_Sales", and "Q4_Sales" have not been correctly applied to the range B9:E9. |
$8,686 | $12,918 | $12,723 | $41,764 | $76,091 | |
