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.

AGK

@Paul Newcome first of all I just want to say thank you for all the phenomenal responses to everyone's questions! You are literally my google :)

I used the function above in an individual cell and it works great! However, I am trying to make it a column function and when I do that I get #invalid value

Paul Newcome

@AGK Are you able to provide a screenshot for context?

AGK

I went to recreate it (I just entered what I wanted manually for now until you had a chance to reply). For some reason when I enter the function in the cell, that cell displays plank even though I can see the function lives in the cell…..weird. But to give more detail because if I took a screenshot, you would get a screenshot of an empty looking cell….

I used =INDEX(ANCESTORS(), 2) because I want to display the grandparent of that particular row. It worked one cell at a time. When I right clicked to convert to Column Formula the entire column displays the invalid error. Would there be a reason why I would not be able to convert to a column formula?

Paul Newcome

Going back through the thread, I see there needs to be a reference to a different column in the ANCESTORS function. Try something like

=INDEX(ANCESTORS([Primary Column]@row), 2)

Then an IFERROR to get rid of the error on rows that are not indented far enough to have at least 2 ancestor rows.

=IFERROR(INDEX(ANCESTORS([Primary Column]@row), 2), "")