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
-
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
-
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))
-
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!
-
You are welcome, and glad this was helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!