Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Parent/Child Row Formulas

Saylor ✭✭✭
edited 12/09/19 in Archived 2016 Posts

I have two questions...


First, I am trying to sum all the values in a column so that when I sort by a specific column the formula does not get all jacked up.


Second, I have another sheet where I trying to sum the values in the child rows to the parent row.  I understand how to do that but everytime I sort the sheet on a different column the "SUM" formula gets jacked up in the parent row.


Help please.


  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭

    Saylor, could you explain in more detail what you mean by "Jacked up"? 

    I'm sure you will get some answers then. 



  • Saylor
    Saylor ✭✭✭

    I apologize.  For example, if I total a column with the formula =SUM([Projected Sales]1:[Projected Sales]30) and then sort on another column the formula may look something like this after I sort it a different way then the way I originally had the sheet sorted when I built the formula...=SUM([Projected Sales]20:[Projected Sales]3) - it jumbles the formula all up.  I know that I can create parent and child relationships and this will solve the problem but wondered if there was a way to keep the integrity of the formula intact no matter how you sort your sheets data.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    If you are sorting, all bets are off. The system assumes you did that on purpose and want the new results.

    Even using Absolute References won't help.


    You can try only sorting rows in a certain range (like children of a summary task)


    I assume you have a mechanism to 'unsort' back to the original view.

    What meaning does the sum have in the sorted view?

    Limit your sort to that area.


    If it has meaning, you may need to define multiple criteria for the sort - to ensure you sort in groups.


    I'd have to see it to add more.




  • Saylor
    Saylor ✭✭✭

    Thanks Craig, that is what I was afraid of.  I created children relationships and used the following formula to fix this issue.  Have a great day.


    =SUM(CHILDREN([Flat Fee PTB]1))

This discussion has been closed.