Illustrated Excel 2019 | Module 10: SAM Project 1b Khatri Family COLLABORATE ON A WORKBOOK GETTING STARTED Open the file IL_EX19_10b_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Illustrated Excel 2019 | Module 10: SAM Project 1b

Khatri Family

COLLABORATE ON A WORKBOOK

• GETTING STARTED

• Open the file IL_EX19_10b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_10b_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_10b_Loans.xml
  • Support_EX19_10b_Loans.xsd
  • Support_EX19_10b_PaidLoans.xml
  • Support_EX19_10b_Purchases.html
• With the file IL_EX19_10b_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.
• To complete this project, you need to display the Developer tab. If this tab does not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the following check boxes: Developer

• PROJECT STEPS

1. Arjun and Darsha Khatri are your neighbors in Indianapolis, Indiana, and have started tracking their credit card charges and loan balances in an Excel workbook. Arjun usually enters the credit card transaction amounts, and Darsha double-checks them against their statements, inserting comments (or "notes," in some versions of Office 365) when she has questions or corrections. Arjun asks for your help in responding to the comments and including missing data.
   Go to the Credit Card worksheet. Show all comments in the workbook, and then respond to the comment in cell C7 as follows:
   1. In cell C7, change the data as Darsha recommends, entering -$100.00 as the amount.
   2. Delete the comment in the cell.
2. Arjun asks you to respond to the other two comments from Darsha as follows:
   1. Go to the next comment in the worksheet, which is from Darsha Khatri in cell E7.
   2. Reply to the comment in cell E7 by including the text Very good, thank you. as the response, and then post your reply.
   3. Reply to the comment in cell F7 by including the text Yes, the balance is correct. as the response, and then post your reply.
3. Arjun notices that an entry in the Merchant Name column might be incorrect and wants to ask Darsha to verify it.
   In cell D6, insert and post a new comment that contains the following text:
   Is this the correct merchant? Please verify.
4. Arjun wants to import some loan data from a webpage where he used to track the family's credit card transactions.
   Import the data from the webpage as follows:
   1. Open the file Support_EX19_10b_Purchases.html in your browser.
   2. Drag to select and copy the four rows of data on the webpage.
   3. In the Credit Card worksheet, select the range A9:D12, and then paste the copied data to match the destination formatting. Close the webpage.
5. Arjun is also tracking current and past loans for the family. He has two XML files containing loan data, and asks you to import them.
   Go to the Loans worksheet. Open the XML Source pane and add the XML schema Support_EX19_10b_Loans.xsd to the workbook.
6. Map the XML elements as shown in Table 1.

Table 1: XML Mappings

 

XML Element	Cell
PURPOSE	B2
LENDER	C2
AMOUNT	D2
BALANCE	E2

 

7. Import the two XML files as follows:
   1. Set the XML map properties to validate imported and exported data against the schema and append new data to existing XML tables. (Hint: Use the XML Map Properties option available on the table's shortcut menu.)
   2. Import the XML data from the file Support_EX19_10b_Loans.xml into the worksheet at cell B2.
   3. Import the XML data from the file Support_EX19_10b_PaidLoans.xml into the worksheet at cell B7.
   4. Close the XML Source pane.
8. Arjun wants to protect the structure of the workbook.
   Protect the workbook structure without using a password.

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.