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
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.

Comments

  • 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.Β 

    Β 

    RichardR

  • 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 ✭✭✭✭✭✭

    Saylor,

    Β 

    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.

    Β 

    Craig

    Β 

  • 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.