Parent Rows: formula to automate identification of parent row

Wendy Young

I'm trying to use one of this formula to automate identification of parent rows:

=IF(COUNT(ANCESTORS([Task Name]1)) < COUNT(ANCESTORS([Task Name]2)), COUNT(ANCESTORS([Task Name]1)))

However, it's telling me that it is #UNPARSEABLE. What am I doing wrong?

Please help! Thank you!

Andrée Starå

Hi Wendy,

To show the Parent, you can use the following formula.

=PARENT([Task Name]@row)

If you want to show the hierarchy, you can use the following formula.

=JOIN(ANCESTORS([Task Name]@row); " / ")

The same version but with the below changes for convenience.

=JOIN(ANCESTORS([Task Name]@row), " / ")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

Andrée Starå

Hi Wendy,

To show the Parent, you can use the following formula.

=PARENT([Task Name]@row)

If you want to show the hierarchy, you can use the following formula.

=JOIN(ANCESTORS([Task Name]@row); " / ")

The same version but with the below changes for convenience.

=JOIN(ANCESTORS([Task Name]@row), " / ")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

Paul Newcome

I don' see a column name of [Task Name]. Try using a column that you have listed in your sheet such as:

=IF(COUNT(ANCESTORS([Goal Subject Matter]1)) < COUNT(ANCESTORS([Goal Subject Matter]2)), COUNT(ANCESTORS([Goal Subject Matter]1)))

Wendy Young

This works great! You're both awesome! Thank you so much!

Paul Newcome

Happy to help. 👍️

Andrée Starå

@Wendy Young

You're more than welcome!

I hope the meeting went well!

Wendy Young

So sorry! One more question on this. I ended up using this formula =JOIN(ANCESTORS([GOAL SUBJECT MATTER]@row), " / ") because it allows me to identify all of the primary (parent) tasks even if they don't children tasks. However, it's blocking my ability to do conditional formatting. From other threads that I've read I believe that it's because it's not a number, but how do I make it a number?

Thank you both so much for all of your help today!

Wendy Young

OH! And the meeting went GREAT thanks to your help! Thank you again!

Paul Newcome

The COUNT/ANCESTORS formula is the one that generates the number.

Wendy Young

Thank you. I tried that one, but it doesn't identify a main/primary task unless it's an official parent task. For example, the last task on this list would need to be counted even though it doesn't have any subtasks.

Paul Newcome

Ah. So you aren't necessarily trying to establish parent rows but more establish those with a hierarchy of zero. Try this...

=IF(COUNT(ANCESTORS([Goal Subject Matter]@row)) = 0, 0)

Wendy Young

YEP! That's perfect! Thank you so much!!! You guys are seriously the best!

Paul Newcome

Happy to help. 👍️

Wendy Young

Hello!

I'm very sorry for requesting help this way, but wasn't sure how else to get assistance. Can one of you please help me with this issue? https://community.smartsheet.com/discussion/comment/251328#Comment_251328

Thank you both in advance and please let me know if there is a better way to do this moving forward.

Wendy

Paul Newcome

@Wendy Young You can always "@ mention" people here in the Community. I went ahead and replied on your other thread.

Wendy Young

THANK YOU!! I should've realized I could do that. My apologies, and thank you again for getting back to me.