Challenge

Case Profilat?$2?$

Support EX$\_5$ squatio axis. suyport EX$\_5\_$Fund$03.$xlsx$,$ Support IX $5$ thand$04.$xlsx$,$

Support$\_$EX$\_3$ hatrematestowy

your help in creating an Excel workbook that can retrieve fund data freas esternal workbooks and

display summary statistics about those funds. The workbook should be acessible to non$-$Excel users.

You'll use the INDIRECT function so that the user needs to enter only the symbol for the fund to get

a summary report. The INDIRECT function is discussed in the "Indirect cerencing" InSight box.

Complete the following.

Open the NP$\_$EX$\_5-5.$xlsx workbook located in the Excel$5>$ Case$2$ folder included with

your Data Files. Save the workbook as NP$\_$EX$\_5\_$Templeton in the location specified by your

instructor.

In the Documentation sheet, enter your name in cell B$3.$ Use an Excel function to enter the cur$-$

rent date in cell B$4.$

Go to the Fund Lookup worksheet. In this sheet, you will create a lookup table with data drawn

from external workbooks.

Open the Support$\_$EX$\_5\_$Fund$01.$xlsx file located in the Excel$5>$ Case$2$ folder. Copy the data

from the range E$2$:P$2$ of the Summary worksheet.

In the NP$\_$EX$\_5\_$Templeton workbook, in the Fund Lookup worksheet, in the range A$4$:L$4,$ paste

a link to the data you copied.

Repeat Steps $4$ and $5$ using the data in the Support$\_$EX$\_5\_$Fund$02.$xlsx$,$ Support$\_$EX$\_5\_$Fund$03.$xlsx$,$

Support$\_$EX$\_5\_$Fund$04.$xlsx$,$ and Support$\_$EX$\_5\_$Fund$05.$xlsx workbooks, pasting links to the

copied data in the ranges $A5$:$L5,A6$:$L6,A7$:$L7,$ and $A8$:$L8$ of the Fund Lookup worksheet.

Assign the named range Fund$\_$Lookup to lookup table in the range A$3$:L$8$ of the Fund Lookup

worksheet.

Copy the data in the range A$2$:C$32$ of the Summary worksheet in the Support$\_$EX$\_5\_$Fund$01$

workbook. In the NP$\_$EX$\_5\_$Templeton workbook, paste a link to the copied data in the range

A$3$:C$33$ of the $30-$Day Data worksheet.

Repeat Step $8$ using data from Support$\_$EX$\_5\_$Fund$02,$ Support$\_$EX$\_5\_$Fund$03,$

Support$\_$EX$\_5\_$Fund$04,$ and Support$\_$EX$\_5\_$Fund$05$ workbooks, pasting links to the copied data

in the ranges D$3$:F$33,$G$3$:I$33,$ J$3$:L$33,$ and M$3$:O$33$ of the $30-$Day Data worksheet.

Select the range $A3$:$O33,$ and then create named ranges from the selection, using the labels in

the top row. $($Hint: Make sure only the Top row check box is selected.$)$

Go to the Statistics worksheet. In this worksheet, you will display information and summary

statistics on a selected fund, In cell B$4,$ enter the text ORTFD for the Ortus fund.

Select cell B$4$ and define a named range using Symbol as the name.

In cell B$5,$ enter the VLOOKUP function to retrieve the name of the fund. Use the Symbol named

range as the lookup value, the Fund$\_$Lookup named range as the lookup table, $2$ as the column

to look up$,$ and FALSE as the type of look up $($exact match$).$