Wednesday, April 6, 2016

My latest Excel spreadsheet

I wanted to know who all in my direct line still needed federal census records. In Legacy I created a search list of my dad’s direct line to include all spouses but not siblings. I had AKAs and married names turned off so that the list wasn’t artificially inflated. I only did my dad’s side because my mother’s side has no one in the United States. I then exported this list as a CSV file. I only included the RIN and name.  Originally I exported the birth and death dates too but it was a bit messy because of the date issue in Excel (I could have overcome this) but I also have a lot of date qualifiers (abt, bef, aft, bet) which mucks things up a bit. I had to look each person up anyway to see what censuses I already had so the dates were no big deal.

I set up the spreadsheet with a header and then froze it so I could scroll without losing my header row. I greyed out the years that the person was not alive and therefore would not appear in a federal census. I put an X if I already a census record for them for that year and an NF if I looked but they were not found for that year. My goal is to attempt to find a census record on every person in my direct line for every year that they were alive. Of course I won’t be able to find them all but I want to make sure that I have at least tried.  I can always go back and try it again later and I might have better luck.

Below is a screenshot from the spreadsheet.  I actually have more census records than what you see.  I have TWO files.  I have my working file and I have my old file still in Legacy 7.5.  I am still transferring things over as I double check the sources, standardization, formatting, etc. As I do, I will be able to fill in more X’s before I get started looking for the missing ones.

ss

 

Copyright © 2016 Michèle Simmons Lewis

30 comments:

  1. I Like your spreadsheet! I have a similar one, and I use S in cells to show ones I have searched for but not found, to keep me from continually re-researching the same ones repeatedly and fruitlessly....

    ReplyDelete
  2. Awesome, Michele! I need to do this too!

    ReplyDelete
  3. I love Excel! What would we do without it?! I did a similar thing to track all of the family members in each census, using Head of Household. Helped me get a good visual of when they died, married, etc. and who was missing that I should go looking for.

    ReplyDelete
  4. This is terrific, Michelle. I'm really inspired by the spreadsheets people are creating for tracking and documenting. Thank you for being generous in your sharing!

    ReplyDelete
  5. I also use a similar spreadsheet, but for my Surname Study. It helps focus the mind (and I need lots of help with that!)

    ReplyDelete
  6. I have a different approach to tracking censuses: I use the census event in my database, from which I generate reports. Those reports get plugged into an excel file.
    The format I use for the census event includes not only location, but also the nature of the census (non-US country, US federal or US state), relationship in household and other explanatory text. And I use placeholders for those census records not (yet) found or missing for a specific reason.
    There is an additional column in my excel census tracking file that I use to group an individual with others in my database into a probable family, specific to a particular census year. This makes it easier to construct searches for a whole household.

    ReplyDelete
    Replies
    1. Would you be willing to share your template?

      Delete
    2. It isn't a template per se. I imported the data from Legacy so the header and the data fields were already filled in. All I added was the census years to the header row and adjusted the column widths.

      Delete
    3. Excellent! Eric Jelle has some excellent forms, too. In fact, I've a few y'all may enjoy at my site: http://shoestringgenealogy.com/form.html

      Delete
  7. Nice idea! I have to make a sheet that covers all the peoples in my own tree.

    In case you find it useful: I created a simple Excel sheet a few years ago that calculates the ages for people based on birth year and automatically grays out the cells using conditional formatting when the person was not alive so I don't have to go through and do it by hand.

    You can see a sample of it here, with a link to a live sheet online that you can download or check out for my formulas and formatting, etc.

    http://currach.johnjtierney.com/2011/11/simple-census-age-table/

    ReplyDelete
    Replies
    1. Nice! I can do simple formulas and conditional formatting but I have a lot to learn :)

      Delete
    2. JJT - When I try to go to http://currach.johnjtierney.com/2011/11/simple-census-age-table/ I get a "403 Forbidden" message. Any other way of seeing your spreadsheet? Sounds like just the thing I'm looking for.

      Delete
    3. I just tried it and it is working for me. Maybe it was just down temporarily?

      http://currach.johnjtierney.com/2011/11/simple-census-age-table/

      Delete
  8. I've been focusing on census research the last few weeks, and I really like what you have done. I'm going to copy your plan, but I think I'm going to include siblings on my search simply because I don't always find my ancestors listed with the same name each decade.

    ReplyDelete
  9. Michele,


    I want to let you know that your blog post is listed in today's Fab Finds post at http://janasgenealogyandfamilyhistory.blogspot.com/2016/04/follow-friday-fab-finds-for-april-8-2016.html

    Have a great weekend!

    ReplyDelete
  10. What is an RIN ?

    ReplyDelete
    Replies
    1. RIN = Record Identification Number. It is an number that database programs automatically assign. Legacy is a true database program so it has RIN numbers. Most (if not all) genealogy programs have these.

      Delete
  11. This is a great idea. I wish our genealogy software programs already incorporated things of this nature to make our searches easier. Just imagine a way to show how you proved people were related, and a checklist to show what records you found and which ones you still need to look for.

    ReplyDelete
    Replies
    1. It's pretty much impossible to have one program that can do everything :)

      Delete
  12. Thank you for this great idea, Michelle. As a novice may I ask a silly question?
    How do I create the search list of a direct line in Legacy initially? I am using the search options but seem to get a 26 page report and know I don't have that many ancestors in the line I am trying to list!!

    ReplyDelete
  13. Helen,
    Go to TOOLS > ADVANCED TAGGING
    Put your anchor person in the box (you). Now select ANCESTORS. Make sure it is on an UNUSED Tag. Now Select DIRECT LINE ANCESTORS. Check Include Other Spouses (if you want). DON'T check any of the other boxes. Click OK. Now they are all tagged.

    Go to SEARCh > FIND. Click the MISC tab. Select the option for INDIVIDUALS WITH TAG #1 (or whatever taf you used). Now CREATE LIST. You now have a search list of your direct line ancestors.

    ReplyDelete
  14. Wonderful, Michelle.
    I now have my spreadsheet set up, a list to consult and a much clearer focus on my research.
    Vertical learning curve ahead with tagging and excell - very chuffed and excited.
    Thank you so very much,
    Cheers from Oz,
    Helen

    ReplyDelete
  15. This is wonderful, and I just did the CREATE LIST and saw my ancestors. Question: how do I transfer that information with the proper column markers (census years) to an Excel spreadsheet?
    Thank you very much,
    Sandy

    ReplyDelete
    Replies
    1. You can transfer the names by saving the list as a CSV file (click Print and you will see the option there). Once I had the list of my ancestors in Excel I added the census columns myself. There isn't a way to do this automatically.

      Delete
  16. Just found this on The Organized Genealogist. It's just what I need but couldn't come up with on my own. Thank you very much for sharing your work.

    ReplyDelete