The Range Free Foods Corporation began operation last year
in Phoenix by opening three stores in different areas of the city. The manager
of each store prepared a workbook that summarizes the firstquarter results. As
the assistant to the general manager, you need to complete the Downtown
workbook, and then link data from the three workbooks to a consolidated
workbook. This exercise follows the same set of skills as used in Hands-On
Exercises 1 and 2 in the chapter. Refer to Figure 9.34 as you complete this
exercise.
a. Open e09p1Downtown and save it as
e09p1Downtown_LastFirst. Click each worksheet tab to see what work has been
done and what work you will do.
b. Click the Jan worksheet tab, press and hold Shift, click
the Mar worksheet tab, and then do the following:
• Click cell A1, click the HOME tab, click Fill in the
Editing group, and then select Across Worksheets. Click Formats in the Fill
Across Worksheets dialog box and click OK.
• Select the range B4:F10 and click Sum in the Editing
group.
c. Click the DowntownQtr1 worksheet, click cell B4, and then
insert a 3-D formula by doing the following:
• Type =SUM(
• Click the Jan worksheet tab, press and hold Shift, and
then click the Mar worksheet tab.
• Click cell F4 and press Ctrl+Enter.
• Double-click the cell B4 fill handle to copy the formula
down the column.
d. Open e09p1South and save it as e09p1South_LastFirst, open
e09p1North and save it as e09p1North_LastFirst, and then open e09p1Quarter1 and
save it as e09p1Quarter1_LastFirst.
e. Click the VIEW tab, click Switch Windows in the Window
group, and then select e09p1North_ LastFirst.
f. Click the Jan worksheet, hold Shift, click the Mar tab to
group the worksheets, and then select Hide.
g. Adapt steps e and f to hide the Jan, Feb, and Mar
worksheets in e09p1South_LastFirst.
h. Click Switch Windows in the Window group and select e09p1Quarter1_LastFirst.
i. Click Arrange All in the Window group, click the Windows
of active workbook check box to deselect it if necessary, and then click OK in
the dialog box.
j. Add links by doing the following:
• Click cell B4 in the Quarter1 worksheet. Type =, display
e09p1Downtown_LastFirst, click cell B4 in the DowntownQtr1 worksheet, and then
press Ctrl+Enter. Edit the formula to change $B$4 to B4. Copy the formula down
the Downtown column.
• Click cell C4 in the Quarter1 worksheet. Type =, display e09p1South_LastFirst,
click cell B4 in the Qtr1 worksheet, and then press Ctrl+Enter. Edit the
formula to change $B$4 to B4. Copy the formula down the South column.
• Click cell D4 in the Quarter1 worksheet. Type =, display
e09p1North_LastFirst, click cell B4 in the Qtr1 worksheet, and then press
Ctrl+Enter. Edit the formula to change $B$4 to B4. Copy the formula down the
North column.
• Format the monetary values with Accounting Number Format
with zero decimal places in the Quarter1 worksheet.
k. Click cell B3 in the Quarter1 worksheet, click the INSERT
tab, click Hyperlink in the Links group, scroll through the list of files,
select e09p1Downtown_LastFirst.xlsx, and then click OK.
l. Adapt step k to create hyperlinks in cells C3 and C4 to
their respective files.
m. Create a footer with your name on the left side, the
sheet name code in the center, and the file name code on the right side of the
Quarter1 worksheet.
n. Save and close the workbooks, and submit based on your
instructor’s directions.