Independent Project 5-6

San Diego Sailing must update its rental rates list and include clients’ names in the bookings worksheet. Both tasks require a link to another workbook.

[Student Learning Outcomes 5.3, 5.4, 5.7]

Files Needed: SanDiegoSailing-05.xlsx and SanDiegoSailingLinked-05.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 5-6.xlsx

Skills Covered in This Project

• Link workbooks with an external reference.
• Edit and copy an external reference.
• Use XLOOKUP in an external reference formula.
• Format values to display four digits.
• Insert a hyperlink.

1. Open the SanDiegoSailing-05 workbook from your student data files and save it as [your initials] Excel 5-6.
2. Open the source workbook.
   1. Open the SanDiegoSailingLinked-05 workbook from your student data files. You will use this as a source workbook and need not save it.
   2. Click the Clients sheet tab. This data include the ID, first and last names, and basic address information.
   3. Click the Rates sheet tab. The current rental rates display on this sheet.
   4. Arrange the two workbooks to be vertically tiled. It does not matter which workbook is on the left but verify which workbook you are working in as you complete each task.
3. Link workbooks with an external reference.
   1. Select cell H5 on the Fleet worksheet in [your initials] Excel 5-6.
   2. Type = and switch to the SanDiegoSailingLinked-05 workbook.
   3. Select the Rates sheet tab and click cell E5.
   4. Press Enter. The half-day rate for the first boat is $412.50.
   5. Select cell H5 and view the formula. It uses an absolute reference to cell E5.
4. Edit and copy an external reference.
   1. Edit the formula in cell H5 to change the absolute reference to a relative reference (Figure 5-99).
   2. Copy the formula to cell I5. The full day rate is $715.00.
   3. Select cells H5:I5 and copy them to reach row 19 without formatting to preserve the borders (Figure 5-100).
5. Link workbooks with an XLOOKUP formula. (Office 365/2021 Note: The XLOOKUP function may not be available in all versions of Office 365 or Office 2021. Build the following VLOOKUP function to complete the Bookings sheet =VLOOKUP(E5,’[SanDiegoSailingLinked-05.xlsx]Clients’!$A$5:$C$31,3,FALSE).)
   1. Select cell F5 on the Bookings worksheet in [your initials] Excel 5-6. From the data on the Clients sheet tab in SanDiegoSailingLinked-05, you can look up the client’s last name.
   2. Click the Lookup & Reference button [Formulas tab, Function Library group] and select XLOOKUP.
   3. Select cell E5 for the Lookup_value. The function will look for this value in the lookup array.
   4. Click the Lookup_array box, switch to the SanDiegoSailingLinked-05 workbook and the Clients sheet, and select cells A5:A31, the ID numbers.
   5. Click the Return_array box, switch to the SanDiegoSailingLinked-05 workbook, and select cells C5:C31 on the Clients sheet (Figure 5-101). When the client ID in cell E5 matches a value in the lookup array, the corresponding name in the return array will result.
   6. Click OK. The first name is Olander.
   7. Double-clilck the Fill pointer for cell F5 to copy the formula to row 19.
6. Format values to display insignificant zeros.
   1. Select cell E5:E19 on the Bookings sheet.
   2. Click the Number launcher [Home tab, Number group].
   3. Choose a built-in format and edit it so that a four-digit ID results (Figure 5-102). (Dates in your workbook will not match the figure.)
7. Insert a hyperlink to open the source workbook.
   1. Close the SanDiegoSailingLinked-05 workbook without saving.
   2. Select the Fleet sheet and click cell B22.
   3. Create a text hyperlink to show Open source data. as the display text.
   4. Navigate to and select SanDiegoSailingLinked-05 workbook and complete the hyperlink.
   5. Right-click the hyperlink and format it bold.
   6. Test the hyperlink.
   7. Close the source workbook without saving.
8. Save and close the workbook (Figure 5-103).