Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Formula in % Complete column

This discussion was created from comments split from: Auto update % complete.

Answers

  • I have the same problem. Why on earth can't we automate the % Complete function? I have hundreds of rows I need to regularly update completion percentage for. It's truly outrageous that there is no way to enter a formula in this column, there is no way to automate the copying of data from another column which shows live updating % complete, and there is no way to get the gantt chart to use the live updating data column i've created to visualize completion against the schedule.

    PLEASE FIX THIS FEATURE

  • Community Champion
    edited 02/04/25

    Hi @Austinc427 & @Heald33

    The restriction comes from the roll-up feature.

    Site faviconParent rollup functionality in sheets | Smartsheet Learning Center

    Parent rollup functionality tips
    You can’t edit the Start Date, End Date, Duration, and % Complete columns in parent rows with dependencies enabled. To edit these values, you can deactivate the dependency functionality on the sheet or create additional columns not affected by dependencies for your calculations.
    You can’t use custom formulas to change the display or bars in the Gantt view.

    A workaround I often use to change the Start column of the dependency-enabled sheet is cell-link. This technique is also often used in Smartsheet Control Center projects.

    For example, in the sheet below, since I can not put =TODAY() at the Star Date column, I create a cell link from a sheet with =TODAY() cell.

    Site faviconSmartsheet

    Using the same technique of cell-linking to the restricted column, % Complete, of a dependency-enable sheet, I created a demo solution to update the value based on the calculated % value of the sheet.

    Site faviconSmartsheet

    In the above sheet, the Parts 5's Completion & was 20%. By changing the value to 24, the new cell value updates the cell-linked sheet below.

    Then, the sheet links out the value to the % Complete column of the original sheet.

    Thus, the %Complete and the Gantt bar display changed accordingly.

    Site faviconSmartsheet

    This solution is suitable for stable projects whose tasks' structure and order do not change often, as deletion or adding rows may disrupt the Link-Out-Link-In setting.

    I would use the Data Mesh App to update the % Complete for more dynamic projects with many tasks or rows.

    Sample Data Mesh Config

    The activity log shows the cell change was done through some apps or APIs, such as Data Mesh, in this case.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions