Assistance Needed for Ancestors Function

KurthSFO

Hello!

I'm fairly new to Smartsheet, but have mastered the very basics well.

I have a need to set up a column to pull in some ancestor information. I've consulted the community knowledge base here and also other online resources. I still am unable to get my formula to work correctly.

Here's a screen shot of my columns:

I figured out that to get the name of the ANCESTOR in Task Name to populate in the Ancestor Row column, my formula in the Ancestor Row cell is:  =INDEX(ANCESTORS([Task Name]3), 1)

Here's what I need help with:

• Convert the formula in the Ancestor Row cell to a column formula (currently I get an error that my syntax isn’t quite right)
• If the cell in Task Name is an Ancestor, then leave the cell in Ancestor Row column blank
• If the cell in Task Name column is blank, then leave the cell in Ancestor Row column blank

It seems like the above should be fairly easy to do, but I’ve spent hours and only get a number of errors to include mostly #unparsable.

I thought I could use an IF statement to start, something like this: =IF([Task Name]@row=”,”,INDEX(ANCESTORS([Task Name]@row),1) to leave blank cells blank and return the text value in Task Name, if there is one. Doesn’t quite get me there. It also doesn’t solve for my other needs either.

Can someone please help?

Thanks so much!

Kurt

Sameer Karkhanis

Not sure what you are exactly trying to achieve with this but in a hierarchy, no matter whether you have a name or blank for your task name, the ancestor (aka root node) will always be the same.

Anyway this should work for what you want:

=IF(ISBLANK([Task Name]@row), "", INDEX(ANCESTORS([Task Name]@row), 1))

Sameer Karkhanis

Not sure what you are exactly trying to achieve with this but in a hierarchy, no matter whether you have a name or blank for your task name, the ancestor (aka root node) will always be the same.

Anyway this should work for what you want:

=IF(ISBLANK([Task Name]@row), "", INDEX(ANCESTORS([Task Name]@row), 1))

KurthSFO

Hi @Sameer Karkhanis!

Thank you so much for taking the time to analyze my needs and respond with a workable formula.

What you've proposed works like a charm! Funny, I went down the ISBLANK path last evening and had a formula very similar created that didn't work. I think my spaces were off or something.

Have a great weekend ahead...and thanks again!

Sameer Karkhanis

You are welcome, and glad this was helpful.