GET IT NOW! for a limited time, use code BLR for $10 off the Bloodline Report
Ancestor Report
Today you'll see some things you probably never thought you would see in practical use. In this third and final episode is the Ancestors Table, and a very cool addition to the Bloodline Report selections.
The Ancestors Table was previously detailed in the Timeline project and is reviewed with changes here. This table is not a derivation of the relationship of each ancestor from the raw data, rather, it's the reverse process; it creates a placeholder for every possible relationship and looks up the name to match. This leaves many blank names especially in more distant generations. The [known] column easily filters them out.
Indexing
The first four columns are numbers that form the key to the binary chain logic:
index ordinal row number, determines Gen and is used to aid output lookup
Gen generation, rounded up log of index base 2
Num index number within the Gen, resets to 1 when Gen changes
Off offset between each ancestor and the previous generation
Gen and Num are used to calculate the binary chain, Off is used to lookup Name.
Extended Binary Chain
The clever trick of this table is to assign a binary id to the father and mother of every generation, then convert it to F's and M's to represent every possible bloodline through as many generations as the table contains. This F/M chain establishes the key to link input to output. The binary number comes from DEC2BIN of [Num]-1 with [Gen]-1 digits.
From parents as generation 2, each generation has POWER(2,gen-1) members; 2 parents, 4 grandparents, 8 great-grandparents and so on. The DEC2BIN function limit is decimal 512 = POWER(2,9), which would limit the table to ten generations - not enough. To add a modest three more generations will increase the rows eight-fold (2^3), from 1024 to 8192. This requires working around the Excel limitation.
To extend the binary range we can split it in two and reassemble the parts. The right part (row 28 below) goes up to the first nine digits. Once it reaches the limit 111111111, it starts over at 000000000, 000000001, etc., filling nine digits from then on. The left part (30) begins adding the tenth and higher digits, 0, 1, 00, 01, etc., each for 512 rows. Note the subtle difference between the two DEC2BIN statements, the right (29) using MOD to cycle the numbers every 512 rows, and the left (31) using ROUNDDOWN to get the whole multiple of 512. binext (32) concatenates left and right, and binsub (33) replaces 0 with F and 1 with M.
Relation
Rel expresses the general relationship. Where Chain is unique, Rel is not; grandmothers each have a unique chain, FM and MM, but share the same relation, gm.
Name
The Name column is rooted in the self or reference individual selected on the Bloodline sheet in cell ref_name. This person and their spouse form generation 1. For consistency, the male, whether self or spouse, is on the first row, followed by the female, see rows 51-54.
Gen 2 and up are derived by XLOOKUP, see rows 55 to 60.
lookup value ref for Gen 2, else the name from offset rows above
lookup array Import[UName]
return array Import[UFather] for "F" or [UMother] for "M"
Bonus Feature 1
In Part 1 of this series, I detailed the Bloodline sheet, but since then have added an important new feature. Recall that one purpose of this project is to support lineage societies, which are based on descendancy from participants in an historical event. A key point in finding candidate ancestors is to determine which ones were alive and of appropriate age to participate. We can do this by defining an age range and comparing birth, death and event years. For now, we must assume that they were in the right place as well as the right time. To confirm that fact would require additional download data such as date of immigration.
Age At War
For simplicity I limited events to US-involved wars, ignoring the modern era, and copied a list from the internet into a table, Wars, with columns [From] year, [To] year and [War], which I modified to include the span and which will be used as a picklist to select a war of interest. This table does not need to face the user, but to be control-compatible, the list must be a cell range, not an array, so I put it on the hidden Controls sheet.
To overlap the person's years and the war years, I first define a min and max age of eligibility. These are optional arguments with defaults set on the About sheet. We can then determine if the person was between min and max age at the start of the war, and that he was still living.
Ancestors Table Column "Age at"
Next step is to make this new feature useful, first by adding it to the Ancestors table. The table can't be sorted but it can be filtered as shown below. This uses the war selected on the Bloodline sheet, which is displayed in the header.
Bloodline Sheet Enhancement
Here there are two new controls:
checkbox to filter males of soldier age
cell with Data Validation picklist to select a war
In addition, the Ancestor picklist formula is updated to make use of these controls.
The checkbox is linked to cell FlagWar.
The picklist source is the Wars[War] column. The name PickWar =IFNA(XMATCH(war_name,war_list),0) stores the index number of the selected war.
Here the ancestor picklist is filtered and displays age at the start of the selected war. The source for this picklist is hidden on the Controls sheet.
The updated picklist formula:
Bonus Feature 2
Another outgrowth of having the age at war data is a new sheet to present the list of all possible participants, with lineal chains. This is called the Candidate List.
This array formula outputs dynamic rows and columns, the columns determined by the maximum generation number of the filtered names.
Conditional Formatting hides same values to produce the grouped effect and displays ">" for those from a more recent than the maximum generation.
Other Updates
Controls Sheet
Picklists, reference tables and control cells all need a place to stay that's secure and inobtrusive. I had hidden some of these elements within the interactive sheets, then decided that they deserved a home of their own. I collected them all on a new sheet and hid the sheet. All the cells and lists are named for intuitive referencing, and each control or input cell on the other sheets is set up with those respective names. The Controls sheet can be unhidden by right-click on any tab.
Tab Navigation with Protected Sheets
One advantage of separating control cells from interactive sheets is that it facilitates tab navigation with sheet protection. This idea is explained in a previous post. The basic steps to enable Tab navigation are:
unlock interactive cells, including inputs and control cells
protect the sheet (Alt+H-O-P), no password (unless necessary)
hit Tab to cycle through unlocked cells
if necessary to edit locked cells or change formats, unprotect the sheet (Alt+H-O-P)
Control cells are how we retrieve the values of controls such as checkboxes, option buttons, combo boxes, etc. Since these cells' values are changed by user interaction, they must be unlocked if the sheet is protected. This results in an awkward situation in which the Tab key cycles true input cells as well as (usually hidden) control cells.
With control cells removed from the interactive Bloodline sheet, it now has only three cells that need to be unlocked, for the three picklist selections. With those cells unlocked and the sheet protected, Tab cycles the three cells. The default protection level still allows "select locked cells", so mouse-click or arrow navigation still works. Tab navigation is a good habit to practice. It's also a quick way to tell if the sheet is protected.
GET IT NOW! for a limited time, use code BLR for $10 off the Bloodline Report
Comments