%Complete with dependencies enabled - Running into limitations :(

GeoSmile :-)

Hello all, 

Kudos to you all for such an active and friendly forum!

I am new to Smartsheet and building an organization-wide task tracker for our accreditation and regulatory compliance for health plans.  I have run into a snag on the build with respect to having dependencies enabled and calculating %complete. 

My department level tables that hold each depts. tasks have a met/not met checkbox column.  I added the %Complete column to have the parent task reflect the level of completion of the child task but the issue is rather than have my users type a percentage complete - I want to link the checkbox to a 100% value. With dependencies enabled I am unable to write a formula in the %Complete column to reflect the box being checked or not. I have tried creating a separate %CompleteCalc Column to write and IF statement that references the check box and assigns a value to the dependency protected %Complete column and am getting unparsable error.  Any suggestions on how to approach this? , I like the idea of keeping dependencies vs writing formulas for every parent and dates associated if possible.  

I tried writing a formula that was IF(<check box> =1, <%CompleteCalc> ="100" AND(<%Complete> = "100" >) 

basically trying to get a write to two cells if the checkbox is checked.

Thanks in advance for any thoughts or considerations!

Best, 

Geoff

 

Shaine Greenwood

Hi Geoff,

You're correct in that formulas can't be placed in any column being used for dependencies, including the % complete column.

If you're looking to write a formula in a different column then I'd recommend checking out our article on creating and editing formulas to understand what type of syntax Smartsheet is looking for in a formula: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

Our errors help article is a great resource when troubleshooting formula error messages: https://help.smartsheet.com/articles/2476176-formula-error-messages

The #UNPARSEABLE error indicates that the formula syntax isn't what Smartsheet is expecting, either due to incorrect cell references or too many parenthesis.

As for what you're wanting your formula to do: there currently isn't a way to have one formula return a value to two different cells. You'd need to build two formulas. There also isn't a way to use formulas in columns already being used for dependencies.

If you're wanting % complete to be automated, you'll need to stop using it with your dependencies functionality. You can remove the % complete column from dependencies from the Project Settings window. More information on dependencies can be found in the help center: https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors 

GeoSmile :-)

Hello Shaine, 

Thank you for the clarification - I figured as much from what I could tell- but was just hoping that perhaps there was a crafty way to get formulas to write to the %Complete column since you can freely type to it with a percentage - and not lose the parent date autofill/sensitivity to duration that dependencies gives you without extensive formulas.

Best,

Geoff

Shaine Greenwood

No problem. When you have a moment, please submit a product enhancement request using the form under Quick links on the right of the community site.

I, and our Product team, would appreciate your feedback on how you'd want to use a formula driven or auto-calculating % complete.