GRANDCHILDREN Function

It would be helpful to be able to get values of GRANDCHILDREN. The function would accept two arguments such that:

arg1: numerical value, which identifies the child level.
arg2: boolean, to indicate inclusivity(TRUE) or exclusivity(FALSE) of next level (up or down the hierarchy).
arg3 [optional]: boolean, to indicate inclusivity up(TRUE) or down(FALSE) the hierarchy.

For instance, in a hierarchy with 5+ levels of indentation, you would be able to use SUM(GRANDCHILDREN(3, FALSE)) and you would SUM all values of 3rd generation exclusively.
Alternatively, SUM(GRANDCHILDREN(3, TRUE, TRUE)) and get the SUM of all values 3rd generation and down (4th, 5th gen).

Sign in to vote!

Find more posts tagged with

Formulas

Idea Submitted

Child Rows

GRANDCHILDREN

FUNCTION

Comments

Paul Newcome

How would this work differently than levering the CHILDREN or DESCENDANTS functions? DO you have screenshots and details for a use case?

Daniel Ochoa

Thanks, Paul!

I envision something that works like the screenshot attached.

Note that the 16% percent lines are adding up the lines #.1.1 below them, and while of course I could reference them manually, with N lines it becomes cumbersome to manage.

If you can share an alternative that would allow to add up only the third generation lines, I would greatly appreciate it.

Paul Newcome

So you are trying to sum up just the percentages? Is there a reason the child data is not rolling up to the parent rows?

Daniel Ochoa

Yes, in this case specifically, I'm looking to add the percentages exclusively. I tried using SUM(CHILDREN()) but it only adds the second level of the hierarchy (the lines #.1) and I'm looking to only add the third level.

Paul Newcome

What would be the actual use case though? I understand what you are trying to do, but I haven't run into a time where I needed to do this before. Do you have some live data and an explanation as to why it is set up the way it is that requires this?

You can also insert a helper column with a column formula to output a number based on the level:

=COUNT(ANCESTORS([Primary Column]@row)) + 1

Then use a SUMIFS along the lines of

=SUMIFS(DESCENDANTS(), DESCENDANTS([Helper Column]@row), @cell = 3)

Or if you want to isolate all percentages (assuming the non-percentages are greater than 1):

=SUMIFS(DESCENDANTS(), DESCENDANTS(), @cell <= 1)

Daniel Ochoa

The use case is that I'm building a sheet that requires this set up to maintain a clean layout.

The reason why I'm building it this way is that there will be up to 12 columns in this matrix, and if I include helper columns then the number of columns will be double and the view would be way too cluttered.

Paul Newcome

The helper column method would only require a single additional column. Even if you were to use additional helper columns, you can hide them after setting them up if you are concerned about clutter.

I've never seen a reason to set a sheet up this way before. I'm curious what the actual use case is. Why is it set up this way? What is the workflow that requires this?

Quick Links

Trending ideas

Automatically Identify, Notify, and Archive Inactive Smartsheet Assets
Problem Smartsheet environments quickly become cluttered with inactive Sheets, Reports, and Dashboards that are no longer viewed or updated. This creates confusion for users and significant overhead for admins, especially in large or regulated environments. There is currently no native way to automatically identify, notify…
Allow Global Updates to build cell linking between sheets
Most customer's metadata houses their profile data, some from intake sheet and some either cell linked in or brought into metadata using formulas. Needs change after design and often the new data point needed comes from non-metadata templates, which can cause a blocker if methods used are those above. If it were possible…
Auto-resizing of Calendar App
Having the ability to auto-resize the Calendar App view based on the widget size is needed. We have a "Due in 7 Days' calendar for our dashboards and would like it to be anchored at the top right. Unfortunately, as currently designed, the calendar takes up a significant amount of space and must be moved to the bottom.