Creating Links and Bookmarks for the Census Index

Part 1 of tutorial

One default setting for Excel may have to be changed to do all the steps in this tutorial. Go Tools | Options and select the Calculations tabs.

Please check that for Calculations, the Automatic radio button is selected. Be aware that sometimes when using formulas in Excel, you can accidently begin to edit the formula in a cell, and when you select another cell or cells, that cell or cells references are added to the formula. To correct it, undo each action until the original formula is shown and then press enter.

Step 1: Using Table2Clipboard copy the census table from a webpage and Paste into excel. The census should appear as enumerated (how it was compiled)

Step 2: After the last column with text, insert theses lines of code, one line per column into the top row.

<a name="001
<a href="census-en.htm#001

Screenshot Step 2.

Step 3: Now highlight the four cells and replicate the cells (Step 9 in tutorial) to the last row of the census data. What you will find is that each new row has the next number along so
<a name="001   will be followed by
<a name="002   and
<a href="census-en.htm#001  
will be followed by <a href="census-en.htm#002   etc etc

Screenshot Step 3.

Step 4: Copy the whole sheet (page) into a new sheet. Good to rename the sheets enumerated and index.

Step 5: On the index sheet remove the first two extra columns with
<a name="001
">   This will give you this.

<a href="census-en.htm#001">Enumerated</a>

Screenshot Step 5.

Step 6: Find the column with their age and move this to the right beyond the current data. Where the age column was create a new column (if none exist) and paste this formula. =(1861-M7)

The first number is the year of the census and the M is the column reference for the newly relocated age column (Column M maybe). Change if any is different. Now replicate the formula to the last row. You will have to check for the ages stated in months etc and replace the content of those cell with a year. These cells will contain #VALUE!

Screenshot Step 6.

Step 7: Sort by surname, the forename, then year of birth. Now make a choice as to which columns you would like to appear in the index.

Screenshot Step 7.

Now follow the tutorial for inserting the table cells for the data for inclusion in the web index. This will give you this where the link is. NOTE: A new window will open so that you can have both tutorials open at the same time.

</td><td><a href="census-en.htm#001">Enumerated</a></td></tr>

Screenshot Step 7b.

Step 8: On the enumerated sheet you still have the last two extra columns with <a href="census-en.htm#001 ">Enumerated</a>  Keep these to the right of the column with the tr closed tags when following the steps in the tutorial. They can be used later.

</td><td>Bath<a name="001"></a></td></tr> ... <a href="census-en.htm#001