Working on formula to return the value of the NEXT CHILD row in PARENT column

Jeana
Jeana ✭✭✭✭✭✭
edited 08/13/20 in Formulas and Functions

I have a column called Project with a PARENT row called PROJECT 1. I am looking for a formula to use in another column (WHATS NEXT) that will return the value of the next CHILD row, until there are not more CHILDREN.

Appreciate any insight on this. Seems like it would be simple but I don't want to COUNT the values I want to return the CONTENT of the next row of the CHILDREN.

THANKS!!

Jeana

Best Answer

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Jeana

    Could you elaborate a bit more, of share us screenshots of what you really intent to do.

    If I understood it right, you want to display a value that is present in the Child row, but this same child row will change as project is evolving and activities in child rows are completed? Is that right?

  • Jeana
    Jeana ✭✭✭✭✭✭

    Here is a screen shot. I'm looking to put a formula in the column on the right to show the content for the Next row in the CHILDREN for the Virtual CHILDREN. Yes, the CHILDREN in this row will vary and there will be another 'set' of children below for another topic.

    Hope that helps. Thanks!


  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    edited 08/13/20

    OK @Jeana

    I assume you have then some kind of % complete column on your sheet then. I had to sort this kind of problem some time ago. So this formula should do the trick:

    =INDEX(CHILDREN([Action]@row),MATCH("Not Complete",CHILDREN([% Complete]@row),0))

    Considering [Action] column is where you have the names that you want to display and [% Complete] column is the column you use to say if the children's task has been completed or not. Adapt this formula to what you already have in your sheet. You can also do the MATCH on some RYGB balls as well...

    Up to you :)

    Hope it helped!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How are you determining when to move to the next row?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How are you determining when to move to the next child row?

  • Jeana
    Jeana ✭✭✭✭✭✭

    Paul, there is a checkbox column (DONE) that will be used to indicate a step is completed and trigger an automated notification.

  • Jeana
    Jeana ✭✭✭✭✭✭

    @David Joyeuse I think I get the idea but I am using a checkbox to determine when the current step is done, not a % Complete column. So when DONE (checkbox) is check I would like the formula for this column to display the next STEP. It will also trigger an automation.

    Does that make sense?

    Jeana

  • Jeana
    Jeana ✭✭✭✭✭✭

    @Paul Newcome THANK you so much. This is working just fine now and it's a formula that will help me in other areas as well. For each Parent now I can see what the next step in for that process. This is huge!

    Thank you,

    Jeana

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!