Nov15

10 Comments

While watching today’s Ancestry.com webinar and seeing Crista Cowan‘s census table, I noodled around on the Google and created a quick version for download. Just enter names, birth and death years and it will calculate the persons age during each census from 1850 to 1940.

Act now and it will also gray out cells for censuses in which the person was not around!

Below is a static version – apparently you can embed Google spreadsheets, but they aren’t editable. Visit this link to use a live version of it.

Updates:

December 16, 2014: I tried the cell protection in Google Sheets again after having trouble getting it working in previous years. Looks like it does work now – with lots of opening and closing of the sheet after making every sharing change, named range creation, and range protection change. I also had to sacrifice a chicken. So, only the GREEN cells are now editable in the sheet.

I found someone had mistakenly overwritten the formula in at least one cell with a number, which I’ve fixed. This new cell protection should avoid that problem in the future.

November 15, 2016: I’ve added columns to show ages up to the present day even if you start at 1790. I have also added a “Census Year Increment” field. Normally this would remain as a 10, and all the years in the column headers would increment by that amount. But, say you wanted to also have the ages handy for years ending in “5” to help you look for peoples in the NY State censuses: Now you can just change the Increment field to a 5 and Voila! Mathematics is your friend!

One thing to note: Google Sheets seems to show protected cells by default with a cross-hatch background that makes it hard to read the cell info. You can click on the View menu at the top of the sheet, then de-select the Protected Cells option to get rid of that cross-hatch. (The non-green cells will still be protected, of course.)

(Don’t worry, I’ve got copies in case the sheet gets borked. And hey you – YES YOU, we all know you don’t have any Walter Melons in you tree so no funny names, Mister Smartypants.)

You can also download a copy to use in your own Excel or Open Office software by clicking File… Download As… and saving it to your computer.

Makes a great stocking stuffer!

10 Comments

  • avatar

    Comment by Crista Cowan — November 15, 2011 @ 3:28 pm

    This is great, John. I’m glad you found the video cast helpful. And I think it’s terrific that you created this spreadsheet! It really is one of my biggest (now not-so) secret weapons of genealogy research.

  • avatar

    Comment by John — November 15, 2011 @ 3:36 pm

    It was helpful – thanks! And that was too good an idea not to set up a sheet right away.

  • avatar

    Comment by Regina Hall Ivie — January 15, 2012 @ 10:56 am

    Thank you for this very nice tool, I’ll be using this.

  • avatar

    Comment by Marian Pierre-Louis — June 30, 2012 @ 7:50 am

    Very helpful! Thanks John!

  • avatar

    Comment by Jean Perera — June 30, 2012 @ 10:10 am

    After reading Marian’s blog this morning, I immediately came to yours and downloaded this tool. Thank you very much for sharing this with everyone. I can see how useful it will be.

  • avatar

    Comment by Jim Gill — June 30, 2012 @ 11:20 am

    Nice job, John. Thank you!

  • avatar

    Comment by Susan — July 2, 2012 @ 4:23 pm

    I downloaded the file – then tried to open and for the first ever a security warning came up saying malicious and could try to take over my computer!!!!

  • avatar

    Comment by John — July 2, 2012 @ 4:41 pm

    @Susan, As the document is nothing more than a Google Doc spreadsheet, there is nothing malicious about it.

    But, I did just run a test by downloading the sheet from Google Docs in Excel format, then opened it in the newest version of Excel. It looks like this latest Excel version has a “Protected Mode” that by default is enabled for any spreadsheet downloaded from the Internet.

    While that is certainly a good thing, the message “Office has detected a problem with this file. Editing it may harm your computer” is VERY poorly written.

    Assuming you are seeing the same message, if you click the “more information” link and read through the Excel help pages on it, you’ll see that it is not saying the file IS malicious, but that it has been put in protected mode by default because it came from the Internet.

    You can click on protected mode view settings and turn off different things, but I have not played with protected mode at all yet. In any case, confusing even to me and I’m an information technology and security professional!

    If you are leery of this or any other Excel file, a good trick would be to download the file, then upload it to your own Google Docs and use it there. Then you would not have to worry about any security risks as all the processing is done on their server and not your PC.

  • avatar

    Comment by Susan — July 2, 2012 @ 9:01 pm

    Thank you.

  • avatar

    Comment by Patricia — December 15, 2014 @ 6:26 pm

    Thank you. Very Neat Idea!

RSS feed for comments on this post. TrackBack URL

Leave a comment