Spreadsheet Files and Documentation Copyright 1992, 2000
Raymond L. Kozub. All rights reserved.
The SpreadPed system consists of two linked spreadsheet files named DOGDATA.xls and PEDIGREE.xls, created with Excel 7.0a running under Windows 95. These can be used with Microsoft Excel or adapted to another compatible spreadsheet program, one of which is presumed to be already installed on the user's computer. In the following, it is assumed the user is familiar with Excel basics.
1. Go to the download page and click on the filenames DOGDATA.xls and PEDIGREE.xls to download the files into the desired directory of your computer disk drive. They may show up with two extra characters (;1) at the end of the .xls extension (attached by our VMS server). These two characters should be ignored by Excel, but can be removed by renaming the files if necessary.
2. Load DOGDATA.xls into Excel first. Then load PEDIGREE.xls. If you would like to see the program work immediately, select DOGDATA.xls under the Window menu and type FIDO in place of X in cell P1. A two-generation "pedigree" will be created in the region F1..Y63 of PEDIGREE.xls, the other spreadsheet. It may be viewed in full by using the Print Preview feature (File, Print Preview). A partial pedigree of one of Fido's "ancestors" can be obtained by typing its code name (FLOOZIE, for example) in cell P1 of DOGDATA.xls. (PLEASE NOTE: The dogs listed in DOGDATA.xls are fictitious; any resemblance to actual life forms, living or dead, is purely coincidental!)
3. Build your own database. Select the DOGDATA.xls window and Unprotect the sheet using the Tools Menu (you will not be able to make entries if the sheet is protected). In Columns D through G there are about 150 tables "stacked" vertically, each with 20 rows. Each table looks like this before data are entered:
CODE NAME: AKC NO.:
CODE NAME: AKC NO.:
PUR. DATE: AMT. PD:
After data are entered (in Columns E and G), a table may look
like this (for dog Mack, for example):
AKC NO.: DL000000/03
BORN: SEPT. 5, 1990 SEX: MALE
REG. NAME: KCK'S MACK THE KNIFE
SIRE: SEL. CH. CEDAR CREEK'S ERIN OF LEE RAY, OFA
CODE NAME: ERIN AKC NO.:
DAM: KCK'S CHARISMATIC CARRIE, OFA
CODE NAME: CARRIE AKC NO.:
BREEDER: SANDRA K. KOZUB
PUR. DATE: AMT PD:
In order to create a five-generation pedigree, a table like this must be filled out for each dog which appears in the first four generations of the desired ancestry (up to 31 tables). The region occupied by each table (D541..G560, for example) is given a NAME (use Insert, Name, Define) which matches the CODE NAME of the subject dog (MACK in this case). IMPORTANT: Each table MUST occupy four columns and MUST have 20 rows, and the first row MUST be the one containing the INDIVIDUAL RECORD title. (If the pedigree program is to work properly, the format of the tables CANNOT be changed unless modifications are also made in all of the pedigree cell formulas!) The minimum information needed in each table to generate pedigrees includes the registered names and code names of the subject dog (for whom the table is named), its sire, and its dam. You may wish to keep the existing (silly) data tables for Fido and his ancestors as references until you are familiar with the system.
In the pedigree, titles for the first four generations are printed on a separate line (under the dog's name), the contents being extracted from the TITLES cell in the table of the subject dog. For the fifth generation, the SIRE and DAM cells of fourth-generation dogs are used for both name and titles (only one line per dog). Thus, all the titles should be included in the SIRE and DAM cells along with the names, in preparation for the time when these dogs may appear in the fifth generation. Separate tables are not needed for dogs appearing ONLY in the fifth generation.
The use of code names requires the data for each dog to be entered ONLY ONCE, regardless of how many pedigrees you wish to build or how many times that dog appears in the same pedigree. It is very important, however, that the code names for the sire and dam be the SAME as the code and table names for their respective INDIVIDUAL RECORD tables. Each dog can have one and only one code name, and there can be no duplication of code names. As your database grows, you may want to check your name list frequently to be sure you have not already used the code name you have chosen for a new entry. Individual tables can be listed and accessed quickly by pressing (Ctrl)G or using (Edit, Go To).
As stated earlier, DOGDATA.xls is provided with blank forms for about 150 dogs. If more forms are needed, a blank form can be copied to make additional forms at the bottom of the stack. Also, as you build your database, it is wise to execute the SAVE command frequently so you do not lose your input data.
4. Make a Table of Contents. A list of all the table names and their locations can be written in the first two columns of the spreadsheet (place the cursor in cell A12, then Insert, Name, Paste, Paste List). This list should be updated at the end of every data entry session.
5. Create a five-generation pedigree. After the INDIVIDUAL RECORDS have been created in DOGDATA.xls, a five-generation pedigree can be created by simply entering the CODE NAME of the desired subject dog in cell P1, as per instructions on the screen. As stated in Step 2 above, the pedigree will be produced in region F1...Y63 of the PEDIGREE.xls window. Portions of the pedigree may be viewed on the screen, but it is most easily viewed by selecting Print Preview under the File menu (or by using the Print Preview icon). It may be printed in a format which is compatible with your printer. The present layout is quite suitable for landscape printing on legal-sized paper, but you may wish to change column widths, row heights, fonts, etc., to suit your own application or taste. This flexibility turns out to be a very nice feature, as dogs with a lot of titles can take more space than is usually available in other pedigree programs.
In this demonstration version, the "dogs" having titles were printed in red in Fido's pedigree, the others in black. After creating the pedigree (and coloring the individual dog names/titles as you desire), you may wish to save it under a different file name (File, Save As), such as Mack.xls, and keep it permanently on file. (The system was set up with two spreadsheets instead of one in order to make it easy to separate permanent files of individual pedigrees from the database.) BE AWARE, however, that this new file is still linked to DOGDATA.xls and will respond to changes made there when both are loaded into Excel. Also, in order to change the colors (Format, Cells,...) it will be necessary to first go to the Tools menu and Unprotect the spreadsheet. To make a new pedigree, simply reload the original PEDIGREE.xls file and type in a new subject dog code name in cell P1 of DOGDATA.xls.
6. Create a pedigree for a prospective litter. For example, if you wish to examine the pedigree of the puppies which would result from breeding bitch Peaches to dog Mack, simply switch to the DOGDATA.xls spreadsheet, fill out and name a table (code name PEACHES/MACK, say) with Mack's and Peaches'code names in the appropriate sire and dam positions, and then type PEACHES/MACK in cell P1. If at least four generations of records (three generations of tables) are present in DOGDATA.xls for both Peaches and Mack, a five-generation pedigree for the prospective litter will be produced, and it can be viewed in PEDIGREE.xls as before.
7. Some possible problems: (1) If the appropriate data for a dog needed for the pedigree is missing in the database, "#REF!" will appear in place of that dog's name and the names of its ancestors in the pedigree. (2) Any blank cells which are read from the tables and printed on the pedigree will be printed as zeros. This probably happens most frequently in the TITLES cell and the cells which feed the small data box at the lower left corner of the pedigree. The problem can be eliminated by going to the tables in DOGDATA.xls and entering a space in each of the cells from which a zero was being printed.
8. If you have questions or problems
which cannot be solved by consulting your Excel documentation or help
we will try to be of assistance. We expect no compensation, but
you find these files to be useful, please make a
donation to your local animal shelter or to a dog rescue
Information on the latter is available at the following website (as
as hundreds of others):
We would also appreciate it if you would "register" by letting us know via e-mail that you downloaded the files.
Thank you, and have fun!
Ray and Sandy Kozub
1260 Claude Loftis Road
Sparta, TN 38583
Excel and Windows 95 are registered trademarks of Microsoft Corporation.