Doing the Newport High School̵=
7;s PTSA
Student Directory
Updated 5/2=
5/07 by
Solicit advertising if you have time. We didn’t do any advertising= this year – it just didn’t generate that much money last year compar= ed to the effort involved.
Edit the Change Form to update deadlines and contact i= nfo, with assistance from the Registrar, and give to PTSA President in plenty of time to get it into the August mailing.
Choose the directory cover art and paste it up, if nec= essary.
As soon as the office opens in August, send the Word document containing the Directory Intro section to the office manager for u= pdating. The introduction is kept in a sepa= rate Word document from the student directory listings, which are created from a mail merge.
After Registration Day, pick up the directory change f= orms, and alphabetize them. At the = start of the school year, get the spreadsheet from the Registrar. Don’t wait until she has don= e all of her address edits (since all address changes should be on your directory ch= ange forms anyway.) On the deadlin= e day, pick up any additional change forms from the office. Plan on at least a week and a half= for printing time at BSD. They wi= ll deliver the directories back to the school, if you request it.
The
BSD can print for about 2 cents a sheet. They can also cut paper. They can’t do any kind of bi= nding other than center-fold staples for the half-size directory, and the maximum page count is 20 for that type of booklet.= The 2005 directory had 44 pieces of paper, so there was no way to do= a center-fold, so we comb-bound them all by hand, which took about 30 hours of work. This year, we did a sid= e-staple binding on a larger format. T= heir machines can feed the covers (on different paper) so make a master cover on plain white paper just like the rest of the pages and have them machine fee= d it on cover stock. Don’t f= orget the back covers – they can be printed on, also. This year our back cover was blank inside and out, but that’s valuable real estate if you want to use it= .
A note about Microsoft Word: if you want to change anything abo= ut the page layout to be different from the pages before or after it (like landsca= pe orientation, margins, headers, footers, etc.) you need to insert a Section Break. This past year, we went from a sma= ll half-page format to a larger full-page format, so the margins were not quite as tight= and I needed fewer section breaks to squeeze everything in. Avoid section breaks entirely, if = you can. (There’s one place= later on where they’re helpful, but that’s at the very end.)
It *really* helps if in Word you make all characters visible: Tools / Options / Vi= ew tab / Formatting Marks, check the All box.
Pre-Process t= he Directory Data in Excel
Open the Excel spreadsheet supplied by the registrar.<= span style=3D'mso-spacerun:yes'> There are several formatting thing= s that are clunky in the district system – the biggest one is that everythin= g is in upper case.
Delete any unused columns, like homeroom information.<= span style=3D'mso-spacerun:yes'> Keep the email columns, even if th= ey aren’t printed in the directory, since the class liaisons always want= this spreadsheet in order to update their email lists.
Select the entire sheet and sort ascending by student = name. (Unlike in past years, this year we combined all the grades, so you could look up a student without knowing the= ir grade or searching through four separate class sections.)
Edit the grade column to replace 09 with 9. (Make it look more like a grade th= an a graduation year.) Maybe use F= r / So / Jr / Sr instead? No, too ma= ny ESL parents who wouldn’t understand those.
Edit the column header of the phone numbers to be Home= Phone. It needs to match the tag in the W= ord mail merge document.
Add a blank column called StudentPhone for student cell phone numbers. The district doesn’t track this, but the change form gives an option for students = to enter it.
Do the edits from the address change forms. (Do now in Excel so you don’= t have to worry about capitalization when you type them in!) A very small number of families wa= nt both parents’ addresses listed. You can either enter a second row for them in the Excel spreadsheet,= or add the second address to the Word document after the merge.
Once you have all the changes edited into the file, th= en the real fun begins. There’= s a lot of reformatting that needs to be done, and the easiest way to do it is = to use Excel formulas to massage the data into the new format. There are too many students to do hand-editing on anything but the most unusual cases. It’s easiest to copy and pas= te the formulas from this doc into Excel rather than typing them in. In each situation, put the proper = header name on the column, put the formula in the 2nd row of that colum= n, then use the Fill / Down command to fill the formula into the cells for all= the students. Notice that several= of these formulas go on for multiple lines, so make sure you’ve got it a= ll when you copy and paste. Also= , be sure to back up your spreadsheet before doing this junk; if anything goes wrong, you want to be able to easily start over without losing your address changes.
Add columns with the header name given on the left, th= en insert the given formula on the right. The italics words should be replaced with a cell reference to the cell containi= ng that information:
Column header name à formula
FirstLetter à =3DLeft(cell with StudentName, 1)
ParentNames à =3DProper(cell with guardian names)
Address à =3DProper(addresses)
Fix up the cities by stripping Zip+4’s down to j= ust the 5-digit code, making the city mixed case, and re-capitalizing WA. This formula does all 3 things at = once and “original” shou= ld be substituted with a reference to the cell containing the registrar-supplied = city:
CityState à =3DSUBSTITUTE(PROPER(IF(LEFT(RIGHT(original,5), 1)=3D"-",MI= D(original,1,LEN(original)-5), original<= /i>)), ", Wa", ", WA")
Fix up the student names by stripping off middle initi= als, some of which end with a period, some end without a period, and some have t= wo initials with periods (like “A.B.”).
StudentNameTemp à =3D
IF(LEFT(RIGHT(= original,3), 1)=3D".", MID(original,1,LEN(original)-4), IF(RIGHT(original,1)=3D".", MID(original,1,LEN(original)-3), IF(LEFT(RIGHT(original,2),1)=3D" ", MID(original,1,LEN(original)-2), original)))
To turn the all-upper-case student names into partiall= y mixed case (LAST, First), add another column called StudentName and use the previ= ous column’s results (the StudentNameTemp column) as the input to this new column:
StudentName à =3DCONCATENATE(UPPER(LEFT(StudentNameTemp, SEARCH(",",StudentNameTemp)-1)), ",", PROPER(RIGHT(StudentNameTemp, LEN(StudentNameTemp) - SEARCH(",&= quot;,StudentNameTemp))))
Select the phone number column. Replace all “(“ with nothing. Replace all “)= ” with “-“.
Hide the unused columns, if you want.
Merge the Dir=
ectory
Entries in Word
In Word, open the master merge file (called “mer= ge students.doc”). Go to T= ools / Letters and Mailings / Mail Merge.
Complete Merge to a New Document. It has a bunch of weird-looking @&= #8217;s that go away soon. Most of the weirdness is just so we’re able to have an index letter in the header= of each page. This is the big A at th= e top of the page that has students with A last names, etc. They’re called “Diction= ary Headers” by the Word Help file, but lots of people call them the R= 20;Phone Book” headers.
Save that newly merged document, close everything else= , and start editing the merged file. Back it up first, of course, so you can start over if necessary.
Edit / Find / More / check Use Wildcards. Enter “\@*\@” (without= the quotes, but with the backslashes) and check the box to Highlight all items = found in the Main Document. Find Al= l. Close the dialog. Don’t change any text select= ion until after you’ve applied the special style for the headers, as described next.
Apply the style called “FirstLetter” from = the formatting toolbar to all those selected characters. If the style doesn’t yet exi= st, choose More… from the bottom of the style dropdown in the toolbar, and create a new style – it’s a “character style” with = a font size of 1, font color of White, and name it FirstLetter. Once you apply this style, you won’t see the letter field unless you select it, but it will show up = in the headers later. If youR= 17;ve created the new style, don’t forget to apply it to the selection, by choosing it in the toolbar.
Do More Editi= ng in the Word Doc
Move your selection to the top of the document and sta= rt on the Replace All’s:
In the Replace command, turn OFF Wild Cards.
Replace all instances of “@” with nothing.= (These were just flags to mark the= First Letter for that funky style sheet thing.)
Replace all “^l^l” with “^l” t= o take out blank lines where student phone numbers would be. (All the “l” character= s in the replace commands are lower case L’s, for Linebreaks.) Repeat the command until Word can’t find any more.
Replace all “^l^p^p” with “^l^p̶= 1; to fix blank lines where address would be.&nb= sp; (Again, those are lower case L’s and P’s, with a caret before each.) Repeat until no= ne are found.
Fix street address capitalization. Make sure “Match Case”= is set to ON. Use the Replace All command and just keep editing the two boxes of stuff to replace. (Notice more lower case L’s = in some of these. Also, Word kee= ps converting to superscripts in this table, but what I’m trying to show is just changing the capitalization from mixed to lower case.)
|
Replace Thi=
s: |
With This:<= o:p> |
|
<space>Ne<space> |
<space>NE<space> |
|
Same for Se to SE |
|
|
Same for Sw to SW |
|
|
Same for Nw to NW |
|
|
<space>Ne^l |
<space>NE^l |
|
Same for Se to SE |
|
|
Same for Sw to SW |
|
|
Same for Nw to NW |
|
|
1St |
1st |
|
2Nd |
2nd |
|
3Rd |
3rd |
|
4Th, 5Th, etc., up to 0Th |
4th, 5th, etc., up to 0th |
Do the last hand-edits. Change names like Mcdonald to be McDonald, and O’brien to be O’Brien. (Look in MR= 17;s and O’s and you’ll find all of them.) These are just in the Parent names, since the student last names are in all capitals. Search for and change “Ii= 221; to be “II” and “Iii” to be “III”, if th= ere are any kids with a royal lineage. If you have any two-address familie= s to add, you can add the second address now.
Set Page Layo= ut and Triple Columns
File / Page Setup: Portrait, set all margins to .75= 221;. Set the gutter to .5” on Lef= t. In the Multiple Pages section, set Mirror Margins, to get the gutter on the binding side of each page. On the Layout tab set “Diffe= rent Odd and Even Page Headers”. Set header and footer .25” from edge.
View / Header and Footer. Select the paragraph mark in the h= eader, then go to menu Format / Tabs. Set the tab stop to 3” for a Centered tab and 5.5” for a Right tab.= You’ll fill in the contents = of the header later. (Note that tab = stops are relative to the left margin, not the edge of the page.)
Go to the Footer and set a Centered tab to 3” fo= r the page number. Insert the page number field (there’s a button on the footer toolbar), and format it to be 12 point (but not bold). Press t= he Format Page # button, and set the beginning page number to one more than the last page in the Introduction document.&nb= sp; (If there are 20 pages in the introduction section, set the starting page number to 21, so that page numbers are continuous.)
Press the button for the Next Footer in the Header / F= ooter toolbar, and do the same steps to put the page number in the Even page footers. Then go back to the = main document.
Format / Columns, triple columns, check “Equal C= olumn Widths”. Set the column widths to 2” each with .25” between columns.
Put the ̶= 0;Phone Book” Letters in the Header
Go to the first page of the document, if you’re = not already there. View Headers a= nd Footers.
If you’re in an Odd header (look above the heade= r area to see if it says Odd Header):
- Press tab to get to center, then type “Student= s”, then press tab again to get to the right margin.
- From the menu choose Insert / Field / StyleRef. Choose the style called FirstLette= r. OK this. This should put an “A”= in the header.
- Format the entire header to be Bold, font size 16.
Click the Next Header button in the Header / Footer to= olbar to get to the Even header.
For an Even header, put the StyleRef to the left and t= he title in the center. Format i= t the same way as above. If the FirstLetter isn’t showing up, make sure the header doesn’t have= the font color set to white and make sure the FirstLetter style isn’t Hidden. That messes everythin= g up.
Add the Class= Lists at the End
When I switched from having each class listed separate=
ly, I
added a few extra pages at the end with lists of students in each class.
Right before final printing, adjust the column breaks = to make every column line up at the top of the page. Just delete any extra paragraph ma= rks if they fall at the top of a column.
Print once using nice sturdy paper and take it to the = copy center.
IDEAS FOR STUFF TO DO IN THE FUTURE:
-= = Put teacher emails next to their names.
-= = Put family emails in? There have been privacy concerns.<= span style=3D'mso-spacerun:yes'> If you add these, offer opting out= on the change form sent to parents. Also, you’d need the change the mail merge document to not use= @ as the marker for the header letters.
-= = Find out how many directories actually sold last ye= ar and only make that number. This y= ear we printed 1100, and there were about 100 left over. You can always reprint if you run = out partway through the year.
-= = Maybe include a map of the school building.
-= = See if the district can give 2 parent’s addresses. That’s a hug= e can of worms.
-= = In Student Listing document, don’t put the Dictionary headings (FirstLetter) so close to the edge. Maybe .5” from edge rather t= han .25”.
-= = Make the removal of student’s initials more robust… we may be stripping too many characters for the 2-initial cas= e. Should probably search for the “.” character and delete everything from one character before i= t to the end of the line.
-= = Figure out a way to do a last-name cross-reference = for different kid/parent last names. Couldn’t figure out a way to automate this, but I’m sure it’s possible.
-= = Put room numbers in the Clubs and Activities listin= gs.
2006’s directory team included:
Brandies Berggren – Membership
Aileen Leo – Distribution