Formula to show only certain parent row names

roxBVL

Hello! I have figured out how to specify if a row is a parent/child row (we call them Project/Tasks).

I am trying to find a formula that will automatically pull from a specific parent hierarchy. In this case, it will always be the parents that have only been indented once (rows 22, 31, and 38). Rows 32 through 37 show what is currently a manual process, and row 23 through 30 is my attempt so far to automate this.

I can manually tell each row which parent row to pull from (=[Task Name]$22), but this is still very time consuming and needs daily maintenance when new rows are added.

=PARENT([Task Name]@row) only pulls the immediate parent name. Is there a way to formulate which parent I want it to pull a name from?

Thanks!

Paul Newcome

Ok. What if we do this (column formula):

=IF(COUNT(ANCESTORS([Task Name]@row))>1, INDEX(ANCESTORS([Task Name]@row), 2))

I've never needed the first column reference before, but maybe it is just being picky for some reason.

Paul Newcome

Try this:

=IF(COUNT(ANCESTORS())>1, INDEX(ANCESTORS(), 2))

roxBVL

https://community.smartsheet.com/discussion/comment/389248#Comment_389248

Thank you! Unfortunately, this returned a blank cell

Paul Newcome

Where exactly are you putting the formula?

roxBVL

https://community.smartsheet.com/discussion/comment/389258#Comment_389258

In the "Bucket Name" column

Paul Newcome

On which row(s)?

Just realized I forgot the column reference. Sorry about that.

=IF(COUNT(ANCESTORS())>1, INDEX(ANCESTORS([Task Name]@row), 2))

roxBVL

https://community.smartsheet.com/discussion/comment/389263#Comment_389263

All of them? Maybe I don't understand the question

Paul Newcome

See my last comment again. I was just editing it. I forgot to include the column reference. sorry about that.

roxBVL

https://community.smartsheet.com/discussion/comment/389266#Comment_389266

Still returns a blank cell 😢

Paul Newcome

Try setting it as a column formula so we can see where exactly it is showing up.

roxBVL

https://community.smartsheet.com/discussion/comment/389271#Comment_389271

Paul Newcome

Ok. What if we do this (column formula):

=IF(COUNT(ANCESTORS([Task Name]@row))>1, INDEX(ANCESTORS([Task Name]@row), 2))

I've never needed the first column reference before, but maybe it is just being picky for some reason.

roxBVL

https://community.smartsheet.com/discussion/comment/389275#Comment_389275

THANK YOU SO MUCH!!!! 🌟🌟🌟🌟🌟

That solved it. Appreciate your help SO much!

Paul Newcome

Happy to help. 👍️

roxBVL

https://community.smartsheet.com/discussion/comment/389280#Comment_389280

Hope this is ok to ask, is there a similar formula to Indicate in the "Task Type" column those same rows as "Site", but all other parent tasks as "Projects", and all children as "Task"? The original formula I was using didn't account for more than one hierarchy.

=IF(COUNT(CHILDREN()) > 0, "Project", "Task")

I tried a few different things with your formula, but since tasks are varying levels of ancestors/children, it's not consistent.

Paul Newcome

Try something like this:

=IF(COUNT(ANCESTORS([Task Name]@row)) = 1, "Site", IF(COUNT(CHILDREN([Task Name]@row)) = 0, "Task", "Project"))