new GRANDPARENT() function

Nik Fuentes

I would greatly appreciate a Grandparent function, especially since PARENT(PARENT()) throws an error. I know we have ANCESTORS(), but that gives you the number of ancestors and I need the specific ancestor value.

Ideally, the GRANDPARENT() function would be GRANDPARENT( [reference], [# of generations back] ), that way it could also serve as a GreatGrandparent [etc] function.

With this would hopefully come a GRANDCHILDREN() function that works identically but in the other direction.

Paul Newcome

You can use INDEX/ANCESTORS to accomplish this.

Great Grandparent

Grandparent

Parent

Child

If you want the child row to reference the "Grandparent", you would use

=INDEX(ANCESTORS(), 2)

Parent would be 3. Great Grandparent would be 1.

Nik Fuentes

@Paul Newcome interesting, I didn't know about that.

I still think there should be a GRANDPARENT() function, though. It would be a lot easier to use, especially in large sheets with multiple lineages in the same column:column reference.

It's also a lot easier to parse.

Paul Newcome

@Nik Fuentes Honestly though it would end up being pretty much the same as far as operation goes. I do think that the overall intention would be more apparent than indexing ancestors though.

But in the meantime... At least now you have a workaround.

Berto D

I'm getting #INVALID VALUE error.

Paul Newcome

@Berto D What exactly is causing the error?

Berto D

Paul, thanks for replying. I found a work around and I'm good. Appreciate your time.

AnneV

I get the #INVALID VALUE error when attempting to use =INDEX(ANCESTORS(), 2) as well. I don't know what's causing it. Any guidance?

Paul Newcome

@AnneV Are you able to provide a screenshot? The most likely cause is that you do not have proper indentations.

When using the INDEX function to pull from the Ancestor rows, think of the leftmost indentations as "1" and then the number goes up as you go out further.

1

..2

....3

......4

=INDEX(ANCESTORS(), 2)

will only work on levels 3 and 4, and it will always pull from level 2.

AnneV

It was the row reference bit that was messing me up. It doesn't really want a row, but instead desired level of ancestry from which to index. Granted, I had some indentation goobers that were highlighted once the formula worked, but the row reference was the root of my issue.

My next question is whether one can use an IF statement to populate that row reference, thus allowing for a quick copy down a section with several levels of ancestry, or even as column formula.

Paul Newcome

@AnneV Yeah. Sometimes that can be a bit misleading. Think of the INDEX function as pulling from an array. Your array is all of the ancestors joined together (not necessarily all of the rows in the sheet).

So instead of having an array of

1

2

3

4

5

Your ancestors may be on rows 1 and 4 in which case your array would be more like this:

1

4

The "row number" portion of the INDEX function is asking you which array entry you want to pull, so in this case you are pulling the second entry which happens to be row 4.

I hope all of that makes sense. If not, feel free to ask for clarification on any (or all) of it.