Search_type = 0 = just says to only find exact matches If_not_found = "N/A" = is just what it will show if the lookup value can't be found So to make this NOT an array you just need to use $E:$E for NAME, $F:$F for BIRTH, or $G:$G for DEATH (and you don't need the TRANSPOSE since it is only 1 value)
Returns the values from column E through G of the row corresponding to where the lookup was found. Then I add the count of values being done above by counting how many times you have the Label "Birth" in the rows starting at B$1 up until this row (B15 in this example) to increment within that generation. This calculation is based on the COLUMN() of "this" cell the formula is in and based on the layout you are using, by subtracting 3 and dividing by 2 it gives the power to which 2 needs to be raised for that generation and multiplies that by the initial Key Value (located at $C$14). TRANSPOSE just converts it to a vertical array instead of horizontal I hope this works, but if it isn't what you wanted please explain what you need a little Sure, no problem. If you don't have XLOOKUP() it can be recreated using INDEX()-MATCH(). If you don't have array formulas you might need to enter CTRL-SHIFT-ENTER or break it into 3 formulas for Name, Birth, and Death instead of the single array I created here (basically instead of $E:$G you need to use $E:$E then $F:$F and then $G:$G). So all you need to do is set 1 cell to the SOSA# you want to start with and it will generate all the others. It assumes you start in the 2nd column with the 1st gen and the data is in the 3rd column and each generation after is in the following pair of columns. I created the following formula based on the general format you have for your tree. Thank you in a bit confused at what exactly you want.
#EXCEL FAMILY TREE BUILDER MANUAL#
Everything seems to be manual and thats quite a bit of data entry. Frankly I can't believe someone hasn't published a more comprehensive template to work this out. Likewise, Id like to find one reference formula (USING the Sosa formula from the previous individual) to just exponentially create the reference so I don't have to type =SOSA 242, =SOSA 484 and onward.Īny Excel super-geniuses willing to feed me formula's? I may be asking too much, but any help or suggestions would help me work it out.
If it doesn't give me an error, it comes back a strange number!
I've tried using HLOOKUP to refer the data, but after about 20 different variations I can't seem to find the right combo. I'd like to use the SOSA number in my table to refer each vital fact to the tree.
#EXCEL FAMILY TREE BUILDER SOFTWARE#
I'm trying to make a family tree builder in excel which will eventually turn into a many tabbed family report based on CSV data converted from a Gedcom (genealogy software file).Įach person in the tree is referred to by a SOSA # (an individual's fathers number is their number x2, the mothers number is x2+1 and so that continues up the tree, with the home individual being #1).
0 kommentar(er)
