One Click Task Completion

Geoff Kennedy

Hi All,

In Project we invariably have different variants of:

- percent complete

- Task Status

- Completion Date

- etc

What I am looking for is a way where say I tick a "Complete" checkbox and:

- percent complete goes to 100%

- Task status goes to complete

- completion date is today

- etc

Find more posts tagged with

Formulas

Support

Comments

Travis

Hi Kite - It is possible to automate fields based on the value in another field but depending on how your sheet is set up, all of what you are asking for might not be possible.

With each example, CB is the Checkbox column name and the referenced cell is in row 1. This cell reference will need to be updated to match the cells in your sheet.

“Percent complete goes to 100%”

Add this formula to your % Complete column:

=IF(CB1 = 1, 1)

This will display 100% (1) if the checkbox (CB1) is checked (1). If you have dependencies enabled and your % Complete column is associated with dependencies, you will not be able to add formulas to the % Complete column. The only way around this is to disassociate the % Complete columns from dependencies.

“Task status goes to complete”

Add this formula to your status column:

=IF(CB1 = 1, “Complete”)

“Completion date is today”

You can build a formula that displays “todays” date if the box is checked but “today” will change each day. If checking the box is the last item to be modified in a sheet, you can use the Modified (Date) system column which displays a timestamp from the last time the row was modified.

The same formula format can be used for automating multiple types of columns. Here is a support tip article on automating RYG balls: http://www.smartsheet.com/blog/support-tip-automate-RYG-balls

Here is a link to our Help Center article on formulas which can help walk you through formulas we support: help.smartsheet.com/customer/portal/articles/775363-using-formulas

Geoff Kennedy

Ok have a solution now. Here is the simplified version of what I have implemented.

Basically with one click I can update the Status and % Complete as well as automatically set other Status's

Here's how it works

Fields Under Admin Control (ie locked to most users)
- "Signoff" (checkbox)
- "On Hold" (checkbox)

Calculated Locked Fields
- "% Comp" (smart interpretation of what Assigned Person thinks ("Est % Comp) but overridden by logic where required)
- "Status" (smart calculation of Status using multiple fields - no need to set this manually now)

User Fields
- "Est % Comp" (what the Assigned To person thinks the percent complete is)
- "Assigned To"
- etc

How it works
- "Signoff" checked then "% Comp" = 100% and "Status" = "Complete"
- "% Comp"
- if Signoff checked then = 100%
- if signoff not checked then use % Compl but can't be more than 99%
- ie no matter what the user has entered into Est % Comp the value in "% Comp" can never be greater than 99% unless the "Signoff" is ticked

- Status is calculated and is equal to
- "Complete" - "Signoff" Checked
- "Received" - "Assigned to" is blank
- "Assigned" - "Assigned to" is not blank

- Complete Date - decided not to automate this but instead to highlight the life out of multiple cells if it was signed off but no date had been set. The reason is that we need some diligence over the Completion Date setting

Techy Details

Formulas

Status Field

=IF(AND((Signoff5 = 1), ([On Hold]5 = 0)), "9. Complete", IF(AND((Signoff5 = 1), ([On Hold]5 = 1)), "8. Cancelled", IF([On Hold]5 = 1, "3. On Hold", IF(AND([% Comp]5 > 0, [Assigned To]5 <> ""), "4. In Progress", IF([Assigned To]5 <> "", "1. Assigned", "0. Received")))))

% Comp

=IF(Signoff5 = 1, 1, IF([Est % Comp]5 > 0.99, 0.99, [Est % Comp]5))

Conditional Formatting
- it is more complex than this but the basics are
- if "Signoff" checked then "Date complete" and "Signoff" cells will be highlighted in bright red

Sam S

Thanks for sharing Geoff - that is a great solution! I am going to try and implement some of this in one of my sheets. Wish me luck!

Geoff Kennedy

Hi Tim,

what is working really well is the final signoff tickbox - where no matter how much the task owner thinks they have completed the task, it can't go to the calculated completed status unless I tick off the Signoff box. I get a daily update to send me any tasks that require signoff

Tim Meeks

Geoff,

Tried to find a solution for somethign similar above. My columns had too many selections/variables to use the formulas. HOwever, I did use your suggestion here: -

"Complete Date - decided not to automate this but instead to highlight the life out of multiple cells if it was signed off but no date had been set. The reason is that we need some diligence over the Completion Date setting"

I had 3 cells that needed to be tied together and be in synch once a task was Complete. I used Conditional Formatting and made the other 2 cells red if they weren't in synch with the one that was updated. It took 6 formulas and I had to put them at the top of the Conditional formatting list, but it's working great!