Smartsheet: Sort by Parent (Ignore Children)

Today’s Tip (2018-08-03): Based on this Community Post.

The user wanted to sort a hierarchy, but only on the Parent Row.

Here’s how:

I created a column named [Sort Order].

I added this formula:

=IF(COUNT(ANCESTORS()) = 0, [Primary Column]@row, COUNT(ANCESTORS()))

to each cell.

It checks if this is a Parent Row (COUNT(ANCESTORS()) = 0) and if it is, it returns the value of the [Primary Column] and if not, returns the ancestor count.

Since Smartsheet sorts by each hierarchy level in turn, since all of the rows at a particular level EXCEPT the Parent, will not sort at all.

Lots of ways to expand this, but knowing there is a solution is the start of that.

Enjoy!