Smartsheet Enhancement Request: BRANCH

2018-08-05Articles

I would like to add a Hierarchy based function: BRANCH.

That is, starting from a many tiered hierarchy and pulling out information needed, easily.

I think I may have a work-around for this lack-of-feature, but I suspect it is prone to breaking by the end-user. It is also not fully tested and adds unnecessary* burden on the processing of the sheet. As this came from an idea sparked while prototyping and not a customer request, I’m devoting my time to defining the request instead of the work-around. In many (most?) cases, everything could roll-up from one hierarchy level to the next, but that could get quite burdensome, many calculations to get the desired end-result. If those intermediary formula results are only needed to serve the final value, then there has to be a better way.

There are ways to get portions of this hierarchy.

The whole column can be retrieved:

And the children of a cell can be retrieved, like this:

or like this:

but there is currently no way to start from the whole list (the column reference) and remove the parts that don’t match the criteria. That leaves working up from the bottom, rolling up each count or sum or whatever I’m trying to gather at the highest level of concern. In the simplest way, if a question that comes up in a meeting “how many statuses do we have?“, and all of the intermediary calculations have not already been created – how long would it take to implement it across all the areas needed?

My guess: longer than the it should. A BRANCH function may speed the time to the answer.

Final Thoughts:

Is there an easy way I’ve overlooked?

This is posted to the Community and hope to solicit responses and feedback there. Please join the conversation if any of this would something you’d like the Smartsheet developers to consider.

The Community Post

Thanks for reading.

* unnecessary burden — that’s just an assumption on my part.

Comments

Kara

Would a “Descendants” function work for your use case? ”
Would be used within another function to reference *all* descendant rows of the referenced parent row.
something like =COUNT(DESCENDANTS([Task Name]1))

    Ronin Global

    In your design, is DESCENDANTS() just the opposite of ANCESTORS()?
    Then no.

    What I believe I was looking for was a way to perform some sort of COLLECT() on a specific level in the hierarchy.
    COUNT(ANCESTORS()) returns how many ancestors I have (but only along one path*), not how many had red hair or live in northern Florida.

    COLLECT() might work, but either a) it didn’t or b) it did, but don’t remember that it did or c) COLLECT came out after I posted this request. I’d have to check.
    I suspect (a)

    In the document – a poorly organized and poorly written spec document – I was working on yesterday, there were a least four levels. Sometimes level 2 was the requirement, sometimes it was a header. Sometimes level 3 was a requirement, sometimes it was a header, same with level 4. Obviously (to me), if the level was a requirement, it had not children.
    What I wanted to see yesterday was all the requirements and the status of them.
    I suppose if =COUNT(DESCENDANTS()) was 0 then that could be used as the requirement. COLLECT( range where COUNT(DESCENDENTS()) = 0), but again, I want to collect the matching range, not the range I had used to COLLECT them. I may be starting to ramble and not make as much sense.
    You’ve got my info Kara if this didn’t help.

    * In Smartsheet terms, from my grandchild view, I only have one grandparent, but that is not true. And that is a different design issue.

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.