Have Status drive Duration in project plan

Sam Minardi

I would like to be able to create functionality in my project plan such that:

In effect, this is designed to keep the task owner's admin activity in the project plan to a bare minimum. All that would need to do is select Status of either In Progress or Complete, everything else is done behind the scenes. I know that in Smartsheet help and learning, it states that "If you enable dependencies in the Start Date, End Date, Duration, % Complete, and Predecessors columns, you can't use formulas", which to me isn't very clear. Does that mean I can't create formulas in those specific columns? Or as in the scenario I describe above, does it mean that I also can't reference/access/change data in one of those columns even if the formula isn't actually in those columns?

Project Plan Example 20240126.png

Thanks,

Sam Minardi

Julie Fortney

You are correct, you can't use formulas in those columns when you have dependencies enabled.

Have you looked into using baselines?

https://help.smartsheet.com/articles/2482093-baselines

Sam Minardi

Hi Julie,

Thanks for your response. Unless I'm not seeing something with Baselines, I don't think that's going to address this problem. The team I'm working with want the functionality of having dependencies enabled, but they think it would be too much of a distraction/opportunity for human error to require the task owners to note the actual duration of a task and then update the Duration column with that number. It looks like short of creating dependency functionality from scratch that includes this automation, this is about as easy as can be done for the task owners.

Sam Minardi

Sam Minardi

Hi @Debbie Sawyer

This post is my initial question about driving duration with Status. Please see attached Word doc.

Have Status Drive Duration in Project Plan.docx

Debbie Sawyer

@Sam Minardi

Hi Sam,

Thank you for the tag in this thread.

From the answers given on your thread I believe you have most things working now. However the last part you require, updating Duration based on a formula, isn't possible with Smartsheet due to restricted columns and enabled dependencies.

We do have a solution though, the use of one of our Instant Apps. I appreciate you may not want to use budget to resolve this issue, but if you have a little budget we do have an app that will allow you to set the value of Duration through the use of a formula.

If you follow this link to our Smarter Controls for Sheets page, have a look at the Instant App called Smarter Formula Loader. There is a PDF explainer sheet there, I believe this is what you are looking for.

Please do reach out if you'd like to discuss this or any of the other apps we have to streamline and enhance peoples experience with Smartsheet.

Kind regards

Debbie