Sub-Row and Formulas

Sarah_Michael
edited 12/09/19 in Smartsheet Basics

Is there a way to remove sub-rows from formula tracking? Ex: We want to track the general Task but not the detailed items below that task.

Can we do this with a formula and generate a single metric number?

 

Thanks for all the help!

 

Comments

  • Yes - and there are a couple of ways to do this. 

     

    Easiest? That depends on what you're trying to accomplish. If you just want to exclude all sub-rows, then I suggest something like what I've shown in this screenshot (count the children, if it's zero than it's a sub-row). 

    If you need finer control over it, I've also had success by creating a column called "Level" and Counting the number of ancestors. That gives you a numeric value for how many levels indented a row is, and you can use that number for conditional formatting, or picking only the levels you care about for reports / formulas. 

    11-13-2019 5-01-08 PM.png

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Sarah,

    To add to Corey's excellent advice. Here's another formula that combines all in one.

    I use this one all the time in my client solutions. I name the column Level and use that to simplify conditional formatting and more.

    Try something like this.

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

    The same version but with the below changes for your and others convenience.

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

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

    I hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.