In this assignment, you will manage a large worksheet and work ontwo workbooks at the same time. You will also add a hyperlink to yourworkbook, format the workbook for sharing, and save the workbook as awebpage and PDF file.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Download, save, and open the workbook named challenging_assignment4.xlsx.

0.000

2

Go To cell M52, and then insert the COUNTIF function. Set the Range as m2:m50 and the Criteria as Staff resulting in 16 sporting events that still require a Referee assigned.

12.000

3

In cell K52, type Events with Unassigned Referees.

6.000

4

Open the downloaded file Referee_Requests.xlsx. Arrange the windows so the files are horizontal, with your challenging_assignment4 worksheetas the active worksheet in the top window. Go to cell A52, split thewindow horizontally. Use Find and Replace to locate the first request'sEvent # from the Referee_Requests.xlsx worksheet. Click in cell M36, and then type Danny Litowitz to assign him as the Referee for that event.

10.000

5

Find the remaining three Event #s listed in the Referee_Requests worksheet, and then enter the appropriate referee for each sports event in column M of your challenging_assignment4 worksheet. Close the Referee_Requests workbook. Maximize your challenging_assignment4 worksheet, and then remove the Split.

10.000

6

At the top of the worksheet, insert a new blank row. In cell A1, type Schedule of Sports Events with Unassigned Referees. Merge & Center the title across the range A1:M1, and then apply the Title cell style.

8.000

7

Create a new table style named Sports Schedule.Format the First Row Stripe using the fifth column, second color. Formatthe Second Row Stripe using the fifth column, third color. Format theHeader Row using the fourth column, fourth color. Set as the defaulttable style for this document.

10.000

8

Apply your custom table style to the range A2:M51 and convert the table to a range.

10.000

9

Simultaneously Autofit columns A:M. Set orientation to Landscape,width to 1 page. Insert a footer with the file name element in the leftsection and page number element in the right section. Documentproperties should include the tags sports schedule.

6.000

10

Move the page break on page 1 after row 37. Repeat row 2 at the top of each page.

10.000

11

Insert a Hyperlink in cell J2 to the downloaded Sports_Coaches.xlsx workbook.

10.000

12

Edit the ScreenTip to read Click here for contact information (no period). Save the workbook.

8.000

13

Close the workbook and then exit Excel. Submit the workbook as