{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Documentation","Donors","Donations","Levels","Thanks Letter","Plea Letter"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":6,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Documentation","index":0,"n_rows":32,"n_cols":2,"rows_kept":32,"cut":false,"hidden":false,"merged":[[2,0,2,1],[4,0,4,1]]}
["Charity Database"]
[]
["This database application enables you to keep track of donors and donations, and makes it easy to generate form letters without using Mail Merge in Word."]
[]
["Although it refers to Donors, it can be used with any organization that has members."]
[]
["For complete instructions, go to:","http://techhelptoday.com/wp-content/uploads/2017/05/How-to-Set-up-a-Charity-Database.pdf"]
[]
["Summary of Tasks"]
["Worksheet","Tasks"]
["Donors","Set the list of donors as a formatted Table\nParse the full names into first and last names\nUse a formula to combine the names in last-first order\nMove a column in a formatted table\nFormat the zip codes correctly\nUse the SUMIFS function to add up total donations\nUse SUMIFS to calculate total donations in 2016\nUse Conditional Formatting to highlight distinguished donors\nAdd a column using VLOOKUP to determine the level for each donor\nUse the Name Manager to add a named range\nCreate a column combining city, state, and zip data\nUse the Forms tool to edit the data\nEncrypt the database"]
["Donations","Set the list of donations as a formatted Table\nAdd a column that will display the year for each donation\nInsert a Slicer to filter the data\nAdd a macro that will print a thank-you letter to a donor\nAdd a button that will run the macro"]
["Levels","Use the tables on this sheet for tasks in the Donors sheet"]
["Thanks Letter","Create a Data Validation List based on a named range\nUse VLOOKUP to lookup the name and address of a donor\nSet Print Area so that only the letter will be printed"]
["Plea Letter","Use the Index function to lookup the name and address of donor\nRecord a macro to print a plea letter\nEdit the macro to print several letters\nAdd a comment to tell people how to use print letters"]
[]
["Table of Skills"]
["Skill","Use"]
["Formatted Tables","Donors and Donations tables"]
["Flash Fill","Convert full name to first and last in Donors table"]
["Slicers","Donors and Donations tables"]
["Forms","Donors table"]
["Comments","Donations table"]
["Conditional Formatting with Formulas","Highlight names of large donors"]
["Data Validation","Donations tables, for Member name and Type of payment"]
["Print Formatting","Member list"]
["Values in Text Strings","Letters"]
["VLOOKUP approximate","Determine level of donors from total contributions"]
["VLOOKUP exact","Add addresses to thank-you letter"]
["Macros","Add name to a thank-you letter"]
["VBA","Send a plea letter to a number of donors"]
["Encryption","Whole spreadsheet"]

{"sheet":"Donors","index":1,"n_rows":17,"n_cols":13,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["Donors (Members)"]
[]
[]
[]
[]
["LastFirst","FullName","First","Last","Address","City","State","Zip","City ST Zip","Total","Total 2016","Status","Lifetime"]
["Ames, Ann","Ann Ames","Ann","Ames","1 Main St.","Stamford","CT",6901,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Stamford, CT 06901"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":75},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":75},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Platinum Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Banes, Bea","Bea Banes","Bea","Banes","2 Main St.","Norwalk","CT",6854,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Norwalk, CT 06854"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":10},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":10},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Silver Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Cole, Cal","Cal Cole","Cal","Cole","3 Main St.","Greenwich","CT",6830,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Greenwich, CT 06830"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":10},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":10},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Silver Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Dawes, Don","Don Dawes","Don","Dawes","4 Main St.","Bridgeport","CT",6601,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Bridgeport, CT 06601"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":100},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":100},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Diamond Circle"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":"Distinguished"}]
["Evans, Ed","Ed Evans","Ed","Evans","5 Main St.","Norwalk","CT",6854,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Norwalk, CT 06854"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":10},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":10},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Silver Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Fox, Fern","Fern Fox","Fern","Fox","6 Main St.","Stamford","CT",6901,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Stamford, CT 06901"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":10},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":10},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Silver Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Gomez, Gary","Gary Gomez","Gary","Gomez","7 Main St.","Newport","RI",2840,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Newport, RI 02840"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":10},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":10},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Silver Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Holmes, Hal","Hal Holmes","Hal","Holmes","8 Main St.","Amherst","MA",1002,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Amherst, MA 01002"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":10},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":0},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Bronze Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Inez, Ira","Ira Inez","Ira","Inez","9 Main St.","Brewster","NY",10509,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Brewster, NY 10509"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":30},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":30},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Gold Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Jones, Jack","Jack Jones","Jack","Jones","10 Main St.","Paramus","NJ",7653,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Paramus, NJ 07653"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":10},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":10},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Silver Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]
["Kohl, Kay","Kay Kohl","Kay","Kohl","11 Main St.","Norwalk","CT",6854,{"$f":"DonorsTable[[#This Row],[City]]&\", \"&DonorsTable[[#This Row],[State]]&\" \"&TEXT(DonorsTable[[#This Row],[Zip]],\"00000\")","v":"Norwalk, CT 06854"},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]])","v":0},{"$f":"SUMIFS(DonationsTable[Amount],DonationsTable[Member],DonorsTable[[#This Row],[LastFirst]],DonationsTable[Year],2016)","v":0},{"$f":"VLOOKUP(DonorsTable[[#This Row],[Total 2016]],YearlyLevels[],2)","v":"Bronze Star"},{"$f":"IF(DonorsTable[[#This Row],[Total]]>=Levels!$A$5,Levels!$B$5,\"\")","v":""}]

{"sheet":"Donations","index":2,"n_rows":16,"n_cols":5,"rows_kept":16,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["Donations"]
[]
[]
[]
["Date","Member","Reason","Amount","Year"]
["2016-01-01","Dawes, Don","Gift",100,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-02-02","Fox, Fern","Dues",10,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-03-05","Evans, Ed","Dues",10,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-04-06","Gomez, Gary","Dues",10,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-05-08","Inez, Ira","Dinner",20,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-06-09","Jones, Jack","Dues",10,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-07-11","Banes, Bea","Dues",10,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-08-12","Ames, Ann","Auction",75,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-09-13","Inez, Ira","Dues",10,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2016-10-15","Cole, Cal","Dues",10,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2016}]
["2017-04-22","Holmes, Hal","Dues",10,{"$f":"YEAR(DonationsTable[[#This Row],[Date]])","v":2017}]

{"sheet":"Levels","index":3,"n_rows":13,"n_cols":2,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["Levels and Other Tables"]
[]
["Lifetime Donation Levels"]
["Amount","Level"]
[100,"Distinguished"]
[]
["Yearly Donation Levels"]
["Amount","Level"]
[0,"Bronze Star"]
[10,"Silver Star"]
[20,"Gold Star"]
[40,"Platinum Star"]
[80,"Diamond Circle"]

{"sheet":"Thanks Letter","index":4,"n_rows":26,"n_cols":5,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
[]
["Friends of the Five Mile River"]
["P.O. Box 999999"]
["Darien, CT 06853"]
[]
[]
[{"$f":"TODAY()","v":"2017-05-04"}]
[null,null,"Data for Letter"]
[]
[null,null,"Inez, Ira",null,"¬ Data Validation List using DonorList"]
[{"$f":"C11","v":"Ira Inez"},null,{"$f":"VLOOKUP($C$10,DonorsTable[],2,FALSE)","v":"Ira Inez"},null,"¬  =VLOOKUP($C$11, DonorsTable, 2, FALSE)"]
[{"$f":"C12","v":"9 Main St."},null,{"$f":"VLOOKUP($C$10,DonorsTable[],5,FALSE)","v":"9 Main St."},null,"¬  =VLOOKUP($C$11, DonorsTable, 5, FALSE)"]
[{"$f":"C13","v":"Brewster, NY 10509"},null,{"$f":"VLOOKUP($C$10,DonorsTable[],9,FALSE)","v":"Brewster, NY 10509"},null,"¬  =VLOOKUP($C$11, DonorsTable, 9, FALSE)"]
[]
[{"$f":"\"Dear \"&C15&\":\"","v":"Dear Ira:"},null,{"$f":"VLOOKUP($C$10,DonorsTable[],3,FALSE)","v":"Ira"},null,"¬  =VLOOKUP($C$11, DonorsTable, 3, FALSE)"]
[]
["Thank you for your recent gift to the Friends of the Five Mile River. Your contribution is very important in our efforts to  rutrn our wonderful  waterway to a pristine and natural state."]
[]
["Please visit our website to learn more about our organization. We offer several resources to teach people about the local area and various conservation efforts. And, if you want to join us and share the enjoyment of improving our small part of the planet, please do."]
[]
["Thanking you again for your generosity,"]
[]
[]
[]
["Natalie Drest"]
["President"]

{"sheet":"Plea Letter","index":5,"n_rows":26,"n_cols":5,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
[]
["Friends of the Five Mile River"]
["P.O. Box 999999",null,"Data for Letter"]
["Darien, CT 06853"]
[null,null,"Start"]
[null,null,1,null,"¬ Row number of first recipient in DonorsTable"]
[{"$f":"TODAY()","v":"2017-05-04"},null,"End"]
[null,null,3,null,"¬ Row number of last recipient"]
[]
[null,null,3,null,"¬   Create a letter for the recipient in this row"]
[{"$f":"C11","v":"Cal Cole"},null,{"$f":"INDEX(DonorsTable[],$C$10,2)","v":"Cal Cole"},null,"¬  =INDEX(DonorsTable, $C$10, 2)"]
[{"$f":"C12","v":"3 Main St."},null,{"$f":"INDEX(DonorsTable[],$C$10,5)","v":"3 Main St."},null,"¬  =INDEX(DonorsTable, $C$10, 5)"]
["Greenwich, CT 06830",null,{"$f":"INDEX(DonorsTable[],$C$10,9)","v":"Greenwich, CT 06830"},null,"¬  =INDEX(DonorsTable, $C$10 9)"]
[]
[{"$f":"\"Dear \"&C15&\":\"","v":"Dear Cal:"},null,{"$f":"INDEX(DonorsTable[],$C$10,3)","v":"Cal"},null,"¬  =INDEX(DonorsTable, $C$10, 3)"]
[]
["It is that time of year where we need your support a little more than usual. So, please, think of our fragile environment, and do your part to help keep it as natural as can be. We look forward to hearing from you, and we very much welcome any donation that you might be able to spare."]
[]
["Please visit our website to learn more about our organization. We offer several resources to teach people about the local area and various conservation efforts. And, if you want to join us and share the enjoyment of improving our small part of the planet, please do."]
[]
["Thanking you again for your generosity,"]
[]
[]
[]
["Natalie Drest"]
["President"]