Spreadsheet Maintenance Notes

0 WARNING:  I was not able to edit these web pages using Internet Explorer.  When I use Firefox, the web page editor works fine.

1The on-line documents are stored in Gary Montante's Google Documents account (which is a free account that can contain one GB of data).

Technical notes:

2 Because of a limitation to the number of times you can reference another on-line spreadsheet, and because the following is a LOT faster, to access data from the "Lar Rieu CERT Grads" spreadsheet, add a new sheet (call it "Names") to your Google spreadsheet into which you want to copy CERT Grads data.

Put the following formula into cell Names!A1:

                =importrange("0AigK-bsfW8g3dFNVWGhST3JnM3BXRUtPN3Z1bUs4VFE","LarRieuCERTGrads")

                                                       A                                                                               B

                          (A) the key assigned by Google to access the CERT Grads spreadsheet

                          (B) name of cells to import (the label is defined in the master spreadsheet)

This imports all the alphabetized data into the cells following cell Names!A1 just as they appear in the "Lar Rieu CERT Grads" spreadsheet from the Alphabetical page.

To look up data, you search for the position of a name on the Names sheet in the FullName's column, then use that position to grab the data from a different column. 

For example, on your main spreadsheet page, assume that cells A1 and B1 contain the first & last name of someone and that you want to copy that persons email address into cell C1:

A1           B1                           C1

Gary      Montante           =INDEX(Names!$I$1:$I$100,MATCH(trim($B1) & ", " & trim($A1),Names!$D$1:$D$100,0))

When found,                     C1 = the value in the Email column (I[x]) at the same position that "Montante, Gary"3 appears in the FullName column (D[x]) on the Names sheet.

3 There is a "hidden row" in the "Lar Rieu CERT Grad" master spreadsheet (row 3), which is not hidden in the populated Names sheet.  You'll see ", " in the row before the first name in the list.  In all the other columns you'll see "--".  When cells A1 and B1 (in the preceding example) are both blank, then the value ", " is searched and found in the names column.  Then when you copy the value from another column  at that position you get "--" instead of an error (Not found) showing.

4 In "Lar Rieu CERT Grad" spreadsheet, the "By Street" sheet gets it data from the Alphabetical sheet.  You can't easily sort it via Google, so we download and use Excel, then Upload the sorted spreadsheet...

·         do main menu: File--> "Download as..." the spreadsheet to Excel, (add the version, "v.xx local", to the local filename)

·         open the local copy with Excel. 

·         On the Alphabetical sheet, add "local" to the version number (used as a double check later...)

·         Fix up the "By Street" page to ensure everything is copied and sorted from the Alphabetical sheet

                                i.           unhide row 4 (row 3 has a -1, so A4=A3+1 starts at 0, then A5=A4+1 or 1)

                              ii.            select row 4

                            iii.            drag/copy row 4 all the way down the sheet

                             iv.            you should have a copy of the Alphabetic sheet (move the warning at the bottom so it doesn't get erased) including the end names!

                               v.            hide row 4

                             vi.            select rows 5 to the end.  Sort with primary key== StreetName, secondary key==StreetNumber

                           vii.            double check

                         viii.            save and close the local worksheet

·         open the original spreadsheet on-line (in case you closed it from downloading above)

·         Use Google main menu: File-->"Upload a new version..." to get the sorted local copy back into the open on-line sheet.  If you don't do this, the sharing "key" (the weird ID Google assigns to a spreadsheet so it can be opened in a secure fashion) would change, breaking the link to other spreadsheets which use the key in the "importrange" function2 . 

·         Erase the word "local" (you added this above in the local copy) from the version number on the Alphabetical sheet.  Now you can be sure you got the local copy back to the internet.

·         Check the hidden rows on both sheets (row 3 on Alphabetical and rows 3-4 on the "By Street" sheets).

·         Check that the range label is still accurate (necessary if you add or remove people!)

                                i.            Google main menu: Edit-->Named Ranges... --> LarRieuCERTGrads

                              ii.            Scroll through the sheet.  It should be selected from the top to the last line of data.  If not, FIX it!

·         Print a copy and verify the PDF looks okay.