0

I have a family tree with 4 generations. I have put them into named groups of Parent and Child, for a child in 1st generation becomes a Parent in 2nd generation and so forth.

I can easily, pending some rules, decide what Parent resides in 1st generation and a list of Children from Parent.

When you work with this for a while you start to recognise a system of Children in the 4th generation, and you then just select that Child and from that choise I can then select (XLOOKUP, VLOOKUP) who is the Parent of selected Child.

My problem comes when my children Peter, Joe, Beatrice, Jim, Joe suddenly gets an overlap. The 2 Joes which normally have 2 different parents now only have 1, due to LOOKUP only finding the first solution.

I can easily make a FILTER of which Parents are linked to a Child, and list them seperately but I need that FILTER inside a Data Validation (Drop Down). I have tried the INDIRECT solution and it didn't work as intended. FILTER(Parent;Child=D5)

The function I'm using right now is XLOOKUP(D1;Child;Parent;"not found";0;1) , that works brilliantly when the family tree is straight up and clear. I select the Child, it finds the Parent, which in turn find the (Grand)Parent, etc. When I do find a Joe it returns the Parent of the 1st Joe in the system, messing up the family trees.

Optimally I would have a Drop Down of the possible Parents of this Joe and then the family tree would continue.

(There's a client and level of IP involved. The specific word is 'Other' and several trees are using the word to describe a group of products. Due to IP I can't change any names or make it Joe 1, Joe 2, etc)

I have 1000s of rows of this, when I've selected a family/product-line for a row that row is done. I can't feasible allocate that Drop Down out. The filter needs to be incl in the Drop Down/Data validation menu. Each time the client returns with a new Workbook, I'd prefer I just copy-paste the logic and easily covering the 1000s of rows.

4 generations and 5 families. If for each generation there'd be a drop down of Parents for a Child, that would solve for those families with a Joe.

UserSpecialMinds
  • 1
  • 1

1 Answers1

0
TRANSPOSE(FILTER(Parent;Child=D1))

Actually the solution was a support outsider, but not column, but rather row wise.

But what about the indetermined length of possible parents?

That is where the spill functionality comes in. Data validation with just the first cell, and reference finished with a #. The # makes it a spill. =C1#

But a Data validation is normally vertical, how do you turn it?

Excel does that by auto.

Peter Csala
  • 17,736
  • 16
  • 35
  • 75
UserSpecialMinds
  • 1
  • 1