Percentages lost when indenting linked cells aka best way for roll up smartsheet

ssuser77
ssuser77
edited 05/03/24 in Formulas and Functions

Hello

I am trying to connect about 50 project plan sheets on one sheet. The requested configuration is a traffic indicator for a customer then the ability to drill down to see each customer project and status then a drill down for high-level milestones.

FYI I have just read about the limitations so I understand eventually we will reach the 5,000 cell limit.

I also see the customer traffic indicator will need a formula if they have more that one project which some do, as well as duration and start date.

I am able to link cells and show both customer and by project indicators however for some projects, when I indent, the percentages are not changed but for other projects and the one in the screenshot, the minute I start to indent to match the original source sheet the percentages start to change.

I am wondering about some of the parent child formulas and if there is something else I need to do for the destinations sheet to be aware of the high level true percentage w/o copying the whole sheet or is it maybe the set up of the sheet by some project managers.

Unless I a missing something, a Summary Report won't allow the columns we need and a Row Report doesn't indent or allow me to select high level project milestones.

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭

    The % complete relies on hierarchy. It calculates off of the direct children. So when you indent, you change the children, thus you change the roll-up.

    high level true percentage w/o copying the whole sheet or is it maybe the set up of the sheet by some project managers.

    "True percentage" is subjective, and not everyone thinks Smartsheet does it correctly, so maybe you need to write your own formulas.

    And yeah, it could be the way different sheets are set up, but of course we need more information about that to direct you.

    Are you rolling this up from individual project sheets? Are you using INDEX/MATCH? Do all of the fields have unique entries (eg, you don't more than one "Scoping" phase, or if you do, you are referencing another column also, like the project name to make the value unique)?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!