Row Expansion with Hierarchy

edited 12/09/19 in Smartsheet Basics

What would be nice is if there was a way to expand/collapse rows by level of hierarchy. This would be very helpful as errors in formulas are discovered and need to be corrected. Currently, you have to expand each hierarchy level to make mass corrections.



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You can right click on the Primary Column header and choose the option to  "Expand all" -- which will expand the entire sheet! 

  • tomas.wunsch

    Yeah, but many times you do not want to expand them all - you just want to expand one level at the time (or one branch).

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

    Hi Chris, Mike & Tomas,

    A possible workaround but it depends on your specific sheet.

    If you can search for something unique, that is on all of the branches or column indicating what level it belongs to. I use this method all the time.

    E.g., I have a construction diary that I built for a client, and it looks the same for every day. If i just want to see a specific level of information, I search for it, and that expands the particular sections. 

    Would that work for you?

    I hope this helps you!


    Andrée Starå - Workflow Consultant @ Get Done


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

    W: | | P: +46 (0) - 72 - 510 99 35

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

  • 1. Right click on the column header

    2. Filter

    3. "name of column" - contains - #

    as "#" appear in all error messages. You can be more precise in the request to select more specific error messages

  • And you may tick the box "❏ Include parent row" for a better understanding of the rows that will appear of course.

  • chrislangley
    edited 08/14/18

    What I have is contracts. Each contract has revenue and cost assigned by a month. Each month may have 1 or more items, those items may have 1 or more subitems. When you're running calculations that eventually SUM(CHILDREN) to parent rows and you have logic calculations to evaluate the status of a contract or child row of that contract, the ability to open all 1st or 3rd level hierarchy takes forever without this function. Excel accomplishes this with the Grouping function. All I am saying is that it would be efficient if the primary column had a tool that would expand all rows by 2nd, 3rd, etc hierarchy level. I have several clients and the largest client has 78 different contracts with a total over 1000 rows of sub-level rows.

    As you're building a solution, the ability to edit "errors" or add new calculations takes extra time with the hierarchy functionality. We're using Smartsheet because we don't have software that does this all for us, and we're having to build the solution as we go. If Smartsheet is going to base their entire product on hierarchy, it only makes sense to be able to drill quickly to any level of hierarchy on that sheet.


  • steven.reed14881
    steven.reed14881 ✭✭✭✭✭


    Here is another possible solution that may work for you. By using this formula (=IF(Column1 = "", "", COUNT(ANCESTORS(Column1))) in a 'Level' column, you will get a number for each row that is at the same level in the hierarchy. You can then set up filters--either inclusive (levels 0-2) or exclusive (just level 2, for example). So I think it would achieve what you are trying to do.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =COUNT(ANCESTORS([Column Name]@row))


    Does the same thing, but cuts out the IF. I've found that the more parts you have, the easier it is to break.

  • chrislangley

    Yes. That would work. But wouldn't it be sweet if you didn't have to use a formula for functionality. My point was more of a suggestion for Smartsheet. I leave my job for another that I don't believe uses SS, so this will be the end of my part of the thread.

    All in all. SS is a good tool.