Trying to access an Ancestor cell (mostly grandparents)

megmeyer

I have struggled grasping how Ancestors works. I have the data below. Each Store # has about 50 'steps' under it, with a few Children that have Children. I want every row to contain the store number of its top-most parent. So, the level 1 is easy enough, it's just the [Store #]@row. Works great.

The level 2 is also easy - PARENT(Store #]@row). Works great.

I can't figure out the children of level 2. Or how to make it one formula that would work for all three. (Though I think I could figure that out with IF statements, if I could get the grandchildren to work).

Still new at SmartSheet, and would really appreciate any insights!

Thanks in advance.

Meg

Jeff Reisman

@megmeyer Here's a column formula that will work for every row on the sheet. Put this in your "Store Number" column on the top row, then right click on it and choose "Covert to Column Formula" at the bottom of the menu.

=IF(ISBLANK(PARENT([Store Number]@row)), [Store #]@row, PARENT([Store Number]@row))

English: If there's no value in the PARENT([Store Number]) cell for this row (or there IS NO parent row for this one,) use the Store # value from this row, otherwise, set this to the same value as the Parent of this cell.

megmeyer

@Jeff Reisman, Thank you so much! I had to remove an extra ,@row, (it was getting unparsable), but it worked perfectly!!

I truly appreciate your quick, and wonderful response!

Meg

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327714#Comment_327714

Ah, yeah - fat finger mistake on my part! I'll edit the answer so the next person who finds this doesn't think that should be there. Glad it worked for you!