Assistance Needed for Ancestors Function

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

Best Answer

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Answer ✓

    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))
    

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Answer ✓

    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))
    
  • 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
    Sameer Karkhanis ✭✭✭✭✭✭

    You are welcome, and glad this was helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!