Completion % Formula Based On Drop Down Options

Nick F.

Am running into a bit of difficulty attempting to input a formula in the sheet I have created. I am wanting to have the completion % column auto populate with predetermined values based on what is selected in a dropdown menu from the 'status' column, i.e., completion % automatically goes to 50% if status is 'in progress'. The completion % column currently requires manual entry and was setup from the standard predecessor/duration from project settings, which won't let me input a formula.

Considered removing the project settings for completion % and recreating a column but I do like the rollup % aspect for the various department aspects.

Andrée Starå

Hi @Nick F.

I hope you're well and safe!

Try something like this. (and continue the pattern for the other options)

=IF(Status@row = "In Progress", 0.5)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Nick F.

@Andrée Starå , yes that does work but when i create a new column. Looks like the issue I am having is due to the below since you can't put a formula into the complete % column when using these settings...

I was hoping to avoid creating another column for completion % since i like the rollup functionality for parent functions.. Do you have a formula for rolling up the completion % for sub tasks into the parent task that is similar to this function?

Andrée Starå

@Nick F.

Excellent!

Happy to help!

You can use a Workflow combined with the Change a Cell Action to update the % Complete column when using the Dependencies feature.

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!