We’ve been sharing our love of the new Smartsheet Column Formula functions over on our Tips and Tricks page.
But how can we use Column Formulas in sheets that use Row Hierarchy?
Row Hierarchy in Smartsheet means that rows can have a parent and child relationships, with the children indented under the parent to as many levels as you need.
How do we fix this? We can use an IF Function to tell the sheet to treat the parent rows differently to the child rows! This way you can write a single formula to perform different actions depending on whether your row is a parent or a child. The example below shows a formula in the Cost column that multiplies Unit Cost by Quantity if the row is a child, and sums the values in the child rows if the row is a parent.
The functions which are used in column formulas are usually created for the child rows. In the sheets that have parent and child relationships the parent rows typically contain different functions to summarise their children, which would interfere with the Column Formula being applied.
The key element here is COUNT(CHILDREN()). This counts the number of children associated with a row. A parent row has children and a child row doesn’t.
In the image above, the light blue rows are children of the dark blue rows and therefore if we apply COUNT(CHILDREN()) to these rows we would get a 0. However the dark blue rows above are the parents and if we apply COUNT(CHILDREN()) here the value would be greater than 0, in this case it is 3.
So if the row is a child,it calculates [Unit Cost]@row * Quantity@row, otherwise it calculates SUM(CHILDREN()).
We cover all this and more in our bite sized training modules. Head over to our training pages for more information.