The high school reunion data has been entered into your Excel workbook and the event is only a few weeks away. Your next step is to create a label for each classmate's family so that you can attach it to the bag that will contain their sweatshirts. You must first obtain the sizes for each attendee and place the order for the sweatshirts with the vendor. The vendor must have at least a week and a half to guarantee delivery.
For this project, you will apply the workbook you have used and updated in Phase 1 and Phase 2. You will organize the data from the Classmates sheet so that some data will be hidden. It must be presented in a sorted fashion. After you have sorted the data, you will enter the sweatshirt size data that you have received from the responding classmates. After you have completed the sweatshirt portion of this task, you will create labels in MS Word.
Before you begin, open the Sweatshirt.doc document and review it. Notice that data has been added indicating what size t-shirt is needed for each individual attending the high school reunion. The table included in this document can be copied directly to an Excel workbook that will allow you to perform functions on the data to produce totals.
Download the Sweatshirt document.
Complete the following tasks for this project:
Open your Individual Project 2 workbook.
Copy the data from the Classmates table to create your Sweatshirt list.
Highlight the Y's in your Attend column of the Classmates table.
Apply conditional formatting to the first cell under the Attend heading so that the cell will have a yellow background if the value in the cell is Y.
Verify that your conditional formatting is working by changing the value to N.
Use Copy and Paste Special to copy this conditional formatting to the rest of the cells in the Attend column.
Copy the data from the Classmates sheet to a third sheet that you will name Sweatshirts.
Now that you have the raw data you need on your sweatshirt sheet, complete the following tasks on this sheet:
Delete the data in A1 and enter your name in cell J1.
Delete the data in B1 and enter the current date in cell J2.
Open the Sweatshirts.doc document (The only names on this list are people who have responded that they are attending the family reunion; to copy this data into your Excel worksheet, you must ensure that the data matches).
Go back to your Excel workbook.
Ensure that you have an active cell somewhere in your table of data (anywhere in the range A6:I72) and activate the Sort feature through the Data menu.
Sort the table by the Attend column in descending order.
Now that you have the names of the families attending at the top of your list, you realize the list is not in alphabetical order. The data in your Sweatshirt document, however, is in alphabetical order. You want to preserve this formatting when you enter the sweatshirt information into this worksheet. To fix this problem, complete the following tasks:
Open the Sort feature.
In the second and third lines, enter Last Names and First Names in ascending order.
Click OK (Now your list is in the same order as your document).
Hide Columns C through G and all rows of families not attending so that the only data you see is families that are attending the high school reunion.
To enter headers before copying the data, use the F5 function key to open the Go To dialog box. Type J4 as your reference and click on OK; enter "Adult" in this cell.
In J5:M5, enter the appropriate sizes for the adult sweatshirts (S, M, L, or XL).
In N4, enter "Child."
In N5, O5, and P5, enter the appropriate sizes for the child sweatshirts (S, M, or L).
Open the Sweatshirt.doc document.
Toggle between the Excel workbook and the Word document to ensure that the names are in the correct order (A shortcut to toggle between active applications is Alt+Tab).
Once you are sure that the names are in the correct order, copy the data from the Word document into the Excel spreadsheet.
Use the AutoFill function to add totals to the bottom of each of the t-shirt columns.
Ensure that I74 is your active cell and that the following function is in it: =SUM(I6:I73) (If your cell has a different value, then you have not correctly completed tasks from Individual Project 2 and should review your instructor's comments and make appropriate corrections).
When I74 is your active cell, there is a small, black button on the bottom right corner of the box. When you hover over this button with your cursor, the cursor will change to a plus sign. Click on this button to activate AutoFill.
While holding down your left mouse button, drag your mouse over the cells to which you want to copy the formula (J74:P74).
As soon as you let go of your mouse button, the results of the copied formulas will show in the cells (If your results are not 5, 33, 24, 11, 6, 4, and 3, check your copied data for errors).
Press CTRL+` to reveal all formulas and functions on the worksheet ( ` is the grave accent key directly to the left of the 1 key on your standard keyboard; this command will show or hide all formulas and functions on a worksheet).
Enter Attending in cell H4.
Select B5 (the First Names heading).
Double-click on the Format Painter brush on your toolbar (Doing this will allow you to quickly copy the formatting of this heading to your new headings).
While Format Painter is activated, click on each of the new headings that you have entered in cells H4, J4, N4, and J5:P5.
Press your ESC key to deactivate the Format Painter.
Use the Merge and Center button on your toolbar to do the following:
Merge and Center the data in H4 across H4 and I4.
Merge and Center the data in J4 across J4:M4.
Merge and Center the data in N4 across N4:P4.
Verify that the headings for Attending, Adult, and Child are each centered above Adult, Child; S, M, L, XL; and S, M, L, respectively (The format of each of these cells should match the original format of your initial headings).
Hide Columns A and B and all rows of data except the totals (The only data on the spreadsheet should be the data you need to order the sweatshirts).
Save your workbook using the correct naming convention.
Now you will create the labels that you want to use for the high school reunion. To do this, you will link the data you have in your Excel workbook to a Word document. Perform the following steps:
Ensure that Word and your Excel workbook are both open.
Organize the data into a separate sheet.
Create a new sheet named Labels.
Locate the Last Names field and the First Names field.
Use any method to copy this data for the family members who will attend the family reunion into the Labels sheet (Only the headings Last Names, First Names, and the data under each of these for the people attending the high school reunion should appear).
Open a new blank document in Word.
Verify that your Excel workbook is open and that you have saved the changes that you made to the Labels sheet.
In Microsoft Word, Select the Mailings Tab
Then select Start Mail Merge->Step by Step Mail Merge Wizards
Select an appropriate address label (e.g., Microsoft 30 per page)
Select Browse in the mail merge wizard and navigate to where you have your workbook saved on your system.
Locate and highlight the workbook, and then click Open.
Choose Labels on the screen and click OK (This will allow you to choose the sheet from your workbook).
To designate the fields to appear in your labels, complete the following:
Select More Items to see your fields.
Insert the Last Name field first.
Select Close.
Verify that the field name has been entered in your first label.
Type a comma and insert a space between the first and last names.
Select More Items and insert your First Names field.
Click on Update All Labels.
To finish the merge, complete the following steps:
Select Edit Individual Labels.
Click OK in the dialog box (This will complete the creation of a new document named Labels).
Save this document as YourlastnameYourfirstnameLabels.doc.
Save copies on your hard drive or removable storage device.
Submit both the IP3 workbook and Labels document.
|
|
|
Adult
|
Child
|
|
Last Name
|
First Names
|
S
|
M
|
L
|
XL
|
S
|
M
|
L
|
|
Anderson
|
Max and Sandra
|
|
1
|
1
|
|
|
|
|
|
Andrews
|
Morris and Anne
|
|
1
|
1
|
|
|
|
|
|
Black
|
Conrad and Glenda
|
|
|
2
|
|
|
|
|
|
Brushing
|
Jim and Sue
|
|
2
|
|
|
|
|
|
|
Byrd
|
Jeff and Linda
|
|
1
|
|
1
|
|
|
|
|
Cannon
|
Mary
|
|
|
1
|
|
|
|
|
|
Carlson
|
George and Norma
|
|
2
|
|
|
|
|
|
|
Crippen
|
Pauline
|
|
1
|
|
|
|
|
|
|
Davidson
|
Bubba and Elizabeth
|
|
|
1
|
1
|
|
|
|
|
Davis
|
Joey and Donna
|
|
2
|
|
|
|
|
|
|
Dean
|
Missy
|
|
1
|
|
|
|
|
|
|
Deery
|
Aunt Lois
|
|
1
|
|
|
|
|
|
|
Denbow
|
Fred and Anne
|
|
1
|
1
|
|
|
|
|
|
Dye
|
Alexander and Lynette
|
1
|
|
1
|
|
|
|
|
|
Easton
|
Jim, Cathy, Caroline, Guy, Max and Dillon
|
1
|
|
1
|
|
1
|
1
|
2
|
|
Eisler
|
John and Kim
|
|
|
1
|
1
|
|
|
|
|
Foster
|
Chris and Martie
|
1
|
1
|
|
|
|
|
|
|
Fugate
|
Chistopher
|
|
1
|
|
|
|
|
|
|
Garcia
|
Harry and Becky
|
|
1
|
1
|
|
|
|
|
|
Gomez
|
Lisa
|
|
1
|
|
|
|
|
|
|
Gunderson
|
Pedro, Jaime and Maria
|
|
2
|
1
|
|
|
|
|
|
Harrelson
|
Major and Anne
|
|
2
|
|
|
|
|
|
|
Hermann
|
Joyce
|
|
1
|
|
|
|
|
|
|
Hopkins
|
Sasha
|
|
|
1
|
|
|
|
|
|
Hunt
|
Judy and Sally
|
|
1
|
|
|
|
1
|
|
|
Jenkins
|
Will and Josephine
|
|
|
2
|
|
|
|
|
|
Jordan
|
Roberto
|
|
1
|
|
|
|
|
|
|
Kaye
|
Bob and Sally
|
1
|
|
1
|
|
|
|
|
|
Martin
|
Chris and Gerald
|
|
1
|
|
|
|
1
|
|
|
Meek
|
Richard, Mary, Patrick and Lauren
|
|
1
|
|
2
|
1
|
|
|
|
Mills
|
Jeff, Jamie, Riley, and Kim
|
|
|
1
|
1
|
1
|
|
1
|
|
Mora
|
Tim and Romie
|
1
|
|
1
|
|
|
|
|
|
Nelson
|
Angelina
|
|
1
|
|
|
|
|
|
|
Nichols
|
Charlie and Cathy
|
|
|
1
|
1
|
|
|
|
|
Noble
|
Leon and Olive
|
|
1
|
1
|
|
|
|
|
|
Nolte
|
Kristi
|
|
1
|
|
|
|
|
|
|
Shetley
|
Will and Sheri
|
|
|
1
|
1
|
|
|
|
|
Smithson
|
Bobby
|
|
|
1
|
|
|
|
|
|
Starr
|
Mike, Sabina, Leo and Alex
|
|
1
|
|
2
|
1
|
|
|
|
Vance
|
Adam, Carrie and Dan
|
|
2
|
|
|
|
1
|
|
|
Wilkens
|
Deitz, Peggy, Ryan and Stacy
|
|
1
|
|
1
|
2
|
|
|
|
Yoder
|
Pete and Beta
|
|
|
2
|
|
|
|
|