%Complete with dependencies enabled - Running into limitations :(

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

Smartsheet Issue1.PNG

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…