Giving a Parent Row a level score


Is it possible to give a parent row a score based on its hierarchy in the row grouping? ie The top level of grouping being level 1, next level of grouping level 2, but only where the row is a parent?

I am currently using =COUNT(ANCESTORS()) but this gives a task a score even if it doesnt have a child.

It would be great to return a level like in the image below.

I have seen it done before but its been a while and cant remember. If anyone could help, it would be appreciated.


Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @RobS,

    This formula should do what you're after:

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

    Using your example data:

    Hope this helps, but any problems/queries then just post! πŸ™‚


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!