A Multiple Sheet Template for a Sports Team

Data file: PR6 Data

You have just gotten a job with the Pacific Northwest Soccer Club, and you quickly realize that there isn’t a consistent way for all the coaches to keep track of their team statistics. To help with this, you decide to make a template for Season Stats for each team. Since you are also the coach of the High Flyers this season, you will need to use the template to enter your team’s statistics into a team spreadsheet.

1. Open the data file PR6 Data and save the file to your computer as PR6 Pacific NW Sports Team.
2. Copy the range B11:G22 in the Season Stats sheet to the same range in the Player Stats sheet.
3. Group the sheets and add the following formulas to both sheets:
4. In C22 and D22, you’ll need to count the Xs in rows 12 through 21. To do this, use a COUNTA formula.
5. In E22 and F22, sum rows 12 through 21.
6. In G12, calculate Goal Percentage by dividing the number of Shots by the number of Goals. This will display an error message because there are zeros in column F. We don’t want to display error messages in the file, so an IF statement that tests the value of column F will solve this problem.
7. Change the formula in G12 with the following three pieces:
8. Test – is F12 greater than zero
9. If the Test is True – divide the number of Goals by the number of Shot
10. If the Test is False – enter a zero
11. Copy G12 down the column through G22. Format these cells as percentages.
12. For an extra challenge, put the “banded row” format back in G12:G22.
13. Ungroup the sheets.
14. Save the file as a template called PR6 Pacific NW Team Template.xltx. Make sure to save your template to your USB and not the default folder for templates on your hard drive!
15. Make a new file using the PR6 Pacific NW Team Template and save it as PR6 High Flyers.xlsx.
16. In the Season Stats sheet, enter the following data:
    • D3 – High Flyers
    • D4 – Fall and the current year (i.e. – Fall 2016)
    • D5 – Pacific Northwest Soccer
17. Enter your name, phone number, and email address in row 8.
18. Make four copies of the Player Statistics sheet. Rename the player sheets Player 1, Player 2, Player 3, Player 4, and Player 5.
19. Group the Player sheets. Enter the following formulas:
20. A formula in D4 that points to cell D3 in the Season Stats sheet. Note: Your formula will be =’Season Stats’!D3:G3 instead of =’Season Stats’!D3 because D3:G3 are merged together.
21. A formula in D5 that points to cell D4 in the Season Stats sheet.
22. A formula in D6 that points to cell D5 in the Season Stats sheet.
23. Ungroup the sheets.
24. Click on the Player 1 sheet. Enter the Player Name: Juan Ramirez. Enter the following data from Table 1:
    Table 1: Player 1 Sheet

    	Played	Started	Shots	Goals
    Game 1	x	x	2	1
    Game 2	x	x	3	1
    Game 3
    Game 4	x
    Game 5	x	x	2	0
    Game 6	x
    Game 7
    Game 8	x	x	1	1
    Game 9	x	x	4	2
    Game 10	x	x	3	3

25. Click on the Player 2 sheet. Enter the Player Name: Zach Johnson. Enter the following data from Table 2:
    Table 2: Player 2 Sheet

    	Played	Started	Shots	Goals
    Game 1	x	x	1	1
    Game 2	x	x	2	1
    Game 3	x	x	1	1
    Game 4	x	x	1	1
    Game 5	x	x	2	0
    Game 6	x	x	5	2
    Game 7	x	x	4	2
    Game 8	x	x	1	1
    Game 9	x	x	4	1
    Game 10	x	x	3	2

26. Click on the Player 3 sheet. Enter the Player Name: Vito Lawrenz. Enter the following data from Table 3:
    Table 3: Player 3 Sheet

    	Played	Started	Shots	Goals
    Game 1	x	x	0	0
    Game 2	x	x	1	1
    Game 3	x	x	2	0
    Game 4	x		1	1
    Game 5	x	x	2	0
    Game 6	x	x	3	1
    Game 7	x	x	2	1
    Game 8	x	x	1	1
    Game 9	x	x	1	1
    Game 10	x	x	1	1

27. Make up information for the names and data in the Player 4 and Player 5 sheets.
28. Go to the Season Stats sheet and click on cell C12.  Enter a 3-D formula to COUNTA (count text) in C12 through sheets Player 1 through Player 5.  Copy the formula in C12 through D22.
29. Change the formulas in C22 and D22 to SUMs.
30. Click on E12.  Enter a 3-D formula to SUM E12 in sheets Player 1 through Player 5. Copy the formulas through F22.
31. Preview the worksheets in Print Preview.  Notice that only part of the data is printing for each worksheet. This is because a Print Area was incorrectly set when the file was first created. You need to clear this Print Area for each worksheet individually (modifying print areas cannot be done on grouped sheets).  Exit Backstage View and for each worksheet, click the Print Area button on the Page Layout tab and select Clear Print Area.
32. Save the PR6 High Flyers workbook.
33. Compare your work with the self-check answer key (found in the Course Files link) and then submit the PR6 High Flyers workbook and PR6 Pacific NW Team Template as directed by your instructor.

Attribution

” 6.5 Chapter Practice” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0