Formula to show only certain parent row names

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!


children.jpg


Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:

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

  • Try this:<\/p>

    =IF(COUNT(ANCESTORS())>1, INDEX(ANCESTORS(), 2))<\/p>","bodyRaw":"[{\"insert\":\"Try this:\\n=IF(COUNT(ANCESTORS())>1, INDEX(ANCESTORS(), 2))\\n\"}]","format":"rich","dateInserted":"2023-08-07T15:43:26+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389248#Comment_389248","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/389248#Comment_389248

    Thank you! Unfortunately, this returned a blank cell

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Where exactly are you putting the formula?

  • Where exactly are you putting the formula?<\/p>","bodyRaw":"[{\"insert\":\"Where exactly are you putting the formula?\\n\"}]","format":"rich","dateInserted":"2023-08-07T15:56:31+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389258#Comment_389258","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/389258#Comment_389258

    In the "Bucket Name" column

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/07/23

    On which row(s)?


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

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

  • On which row(s)?<\/p>","bodyRaw":"[{\"insert\":\"On which row(s)?\\n\"}]","format":"rich","dateInserted":"2023-08-07T15:58:57+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389263#Comment_389263","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/389263#Comment_389263

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    再次见到我最后的评论。我只是编辑它。I forgot to include the column reference. sorry about that.

  • See my last comment again. I was just editing it. I forgot to include the column reference. sorry about that.<\/p>","bodyRaw":"[{\"insert\":\"See my last comment again. I was just editing it. I forgot to include the column reference. sorry about that.\\n\"}]","format":"rich","dateInserted":"2023-08-07T16:00:19+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389266#Comment_389266","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/389266#Comment_389266
    blank.jpg

    Still returns a blank cell

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Try setting it as a column formula so we can see where exactly it is showing up.<\/p>","bodyRaw":"[{\"insert\":\"Try setting it as a column formula so we can see where exactly it is showing up.\\n\"}]","format":"rich","dateInserted":"2023-08-07T16:08:20+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389271#Comment_389271","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/389271#Comment_389271
    parent1.jpg


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

  • Ok. What if we do this (column formula):<\/p>

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

    I've never needed the first column reference before, but maybe it is just being picky for some reason.<\/p>","bodyRaw":"[{\"insert\":\"Ok. What if we do this (column formula):\\n=IF(COUNT(ANCESTORS([Task Name]@row))>1, INDEX(ANCESTORS([Task Name]@row), 2))\\n\\nI've never needed the first column reference before, but maybe it is just being picky for some reason.\\n\"}]","format":"rich","dateInserted":"2023-08-07T16:20:56+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389275#Comment_389275","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/389275#Comment_389275

    THANK YOU SO MUCH!!!!

    That solved it. Appreciate your help SO much!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • Happy to help. 👍️<\/span><\/p>","bodyRaw":"[{\"insert\":\"Happy to help. \"},{\"insert\":{\"emoji\":{\"emojiChar\":\"👍️\"}}},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2023-08-07T16:30:01+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389280#Comment_389280","embedType":"quote"}"> 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
    Paul Newcome ✭✭✭✭✭✭

    Try something like this:

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
It would look like this:<\/p>

=COUNT(DISTINCT(COLLECT([Release Set No.]:[Release Set No.], [Release Set No.]:[Release Set No.], CONTAINS(\"Annual\", @cell))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108611,"type":"question","name":"Formula to show only certain parent row names","excerpt":"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…","snippet":"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…","categoryID":322,"dateInserted":"2023-08-07T15:36:49+00:00","dateUpdated":null,"dateLastComment":"2023-08-07T19:15:06+00:00","insertUserID":164670,"insertUser":{"userID":164670,"name":"roxBVL","title":"Head of Project Management","url":"https:\/\/community.smartsheet.com\/profile\/roxBVL","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtet_1_O3hfNEQvdhSwdf3unZ2EL9OFZQ82YyWNvl3Hg=s96-c","dateLastActive":"2023-08-07T19:49:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":17,"countViews":81,"score":null,"hot":3382868515,"url":"https:\/\/community.smartsheet.com\/discussion\/108611\/formula-to-show-only-certain-parent-row-names","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108611\/formula-to-show-only-certain-parent-row-names","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108611,"commentID":389331,"name":"Re: Formula to show only certain parent row names","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389331#Comment_389331","dateInserted":"2023-08-07T19:15:06+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9IO3X6Y36SV6\/children.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"children.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-07T19:49:50+00:00","dateAnswered":"2023-08-07T16:20:56+00:00","acceptedAnswers":[{"commentID":389275,"body":"

Ok. What if we do this (column formula):<\/p>

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


<\/p>

I've never needed the first column reference before, but maybe it is just being picky for some reason.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108606,"type":"question","name":"How do I calculate the count of project days within a quarter given the project start and end dates?","excerpt":"I have a sheet with all of the current projects, each project is along one row. I'm looking to create a column formula that is a count of project days if the project falls within a specific quarter. I've tried to type this out but cannot get the syntax right in smartsheets. I was thinking it would be one IFS statement with…","snippet":"I have a sheet with all of the current projects, each project is along one row. I'm looking to create a column formula that is a count of project days if the project falls within…","categoryID":322,"dateInserted":"2023-08-07T14:45:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-07T15:41:06+00:00","insertUserID":164666,"insertUser":{"userID":164666,"name":"mlj","url":"https:\/\/community.smartsheet.com\/profile\/mlj","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-07T20:01:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":26,"score":null,"hot":3382842987,"url":"https:\/\/community.smartsheet.com\/discussion\/108606\/how-do-i-calculate-the-count-of-project-days-within-a-quarter-given-the-project-start-and-end-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108606\/how-do-i-calculate-the-count-of-project-days-within-a-quarter-given-the-project-start-and-end-dates","format":"Rich","lastPost":{"discussionID":108606,"commentID":389244,"name":"Re: How do I calculate the count of project days within a quarter given the project start and end dates?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389244#Comment_389244","dateInserted":"2023-08-07T15:41:06+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-07T20:01:21+00:00","dateAnswered":"2023-08-07T15:41:06+00:00","acceptedAnswers":[{"commentID":389244,"body":"

There is no IFS function in Smartsheet. Try this instead.<\/p>


<\/p>

=MAX(NETWORKDAYS(MAX([Start Date]@row, DATE(2023, 01, 01)), MIN([End Date]@row, DATE(2023, 03, 31))), 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions