Looking for solution to "Parent vs Ancestors" problem

LelandSwift

Hello,

I have been having tons of trouble coming up with a formula that will give me the desired result. The goal, it to populate a column with two pieces of information from other columns.

In the attached image, the column "project name and header" is supposed to take the ancestor of SLS Job Number and the ancestor of Project, and combine them with a "-" in the middle.

I have tried several formulas, but none are giving the desired results. As seen in the attached photo, I cannot seem to find out how to not include the subheaders from the parent rows.

InkedSmartsheet solution.jpg

Does anyone have any ideas?

Darren Mullen

@LelandSwift Does this tutorial video help you?

https://youtu.be/8GhBTDUOKkY

Darren Mullen

@LelandSwift What is the formula that you are currently using?

LelandSwift

@Darren Mullen The current column formula is If (ancestors@row < 1, [SLS Job Number]@row + "-" + Project@row, PARENT([SLS Job Number]@row) + "-" + PARENT(project@row))

The ancestors row is just an ancestor count. I recognize that the PARENT function will only ever give me the subheaders, but couldn't use ANCESTOR in the same place without error. I guess ultimately, I am looking for a way to return the parent, if there is only one ancestor, or the parent of the parent, if there is more than one. Thanks for the help!

Darren Mullen

@LelandSwift Does this tutorial video help you?

https://youtu.be/8GhBTDUOKkY

LelandSwift

@Darren Mullen Thank you, this worked perfectly. It was the index function, alongside the fact that Ancestors is a range that really helped. The example in the video you linked was absolutely perfect! I appreciate your help!

Darren Mullen

@LelandSwift Great! Glad that it worked for you. Thanks for the inspiration for me to make another video :)