Description

Narbonne Suites

GENERATING REPORTS FROM MULTIPLE WORKBOOKS

⦁

⦁ GETTING STARTED

⦁ Open the file NP_EX19_5a_FirstLastName_1.xlsx, available for download from the SAM website.

⦁ Save the file as NP_EX19_5a_FirstLastName_2.xlsx by changing the “1” to a “2”.

⦁ 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:

⦁ Support_EX19_5a_2020_Sales.xlsx

⦁ Support_EX19_5a_Locations.docx

⦁ With the file NP_EX19_5a_FirstLastName_2.xlsx 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.

⦁ 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

⦁ Edra Kinsman is the sales manager for Narbonne Suites hotel chain, which has locations in Washington, Oregon, Idaho, and Montana. Edra is using multiple worksheets to summarize sales data by state.

Break the external link in the worksheet, so that the formulas in the range B4:B7 of the Manager Information worksheet are replaced with static values. Then switch to the Manager Information worksheet.

⦁ In cell C5, remove the hyperlink, leaving the unlinked text "Bozeman, MT" in the cell.

⦁ In cell D7, create a hyperlink to an email address as follows:

⦁ Link to the email address:

washingtonmgr@example.com

⦁ Use washingtonmgr@example.com as the text to display.

⦁ Use Email the statewide manager for Washington as the ScreenTip text.

⦁ In cell B9, create a hyperlink to the Narbonne Suites Locations listing as follows:

⦁ Link to the file Support_EX19_5a_Locations.docx.

⦁ Use Narbonne Suites locations as the text to display.

⦁ Use View the current list of Narbonne Suites Locations as the ScreenTip text.

⦁ Edit the hyperlink in cell B10 as follows:

⦁ Use Narbonne Suites website as the display text.

⦁ Use View the public-facing website for Narbonne Suites as the ScreenTip text.

⦁ Edra wants to apply consistent formatting to the worksheets submitted by each of the statewide managers. Group the Idaho, Montana, and Oregon worksheets together and then make the following formatting updates:

⦁ Change the font size in the merged range A1:F1 to 18 point.

⦁ Apply the 20% - Accent 6 cell style to the merged range A2:F2.

⦁ Bold the values in the range A6:A10.

⦁ Apply the Accounting number format with zero decimal places and $ as the symbol to the range B6:F10.

⦁ Resize the column width of column B-F to 12. Do not ungroup the worksheets.

⦁ With the Idaho, Montana, and Oregon worksheets still grouped, update the worksheet as follows:

⦁ In cell A6, change the text "In Person" to read:

Walkup

⦁ In cell A9, change the text "Other" to read:

Other referrals

Do not ungroup the worksheets.

⦁ With the Idaho, Montana, and Oregon worksheets still grouped, create a formula as follows:

⦁ Enter a formula in cell B10 using the SUM function that totals the sales for Q1.

Instruction Files