Formula to always reference the parent of a child row within a different column?



I am trying using the parent/child relationship to group a set of data by month and am using the the formula to rank the children rows by their value:

=IFERROR(RANKEQ(Value@row, CHILDREN(Value$105), 0), "")

But I was wondering if there is a way to make this as a column formula? I would like it to always rank the value of the children but need to manually select the parent row.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Assuming your parent rows are unique, my suggestion would be a helper column that replicates the parent row on all of the child rows.

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

    Then you can work in a COLLECT function for your range.

    =IFERROR(RANKEQ(Value@row, COLLECT(Value:Value, [Parent Helper]:[Parent Helper], [Parent Helper]@row), 0), "")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!