Formula for: %Complete to be automatically changed to 100% when Status is updated to Complete
Hello community!
I'm looking for this formula:
%Complete to be automatically changed to 100% when Status is updated to Complete
_ and, I don't think I can put a formula in the %complete column so where would I put it???
Thanks!
Best Answer

Hi Lynda,
I recently tried to do this very thing to my sheet and also encountered the same error  % Complete column not allowing an equation. I found out that if you change "Options" from "% Complete" to "None" in the Project Settings, I was finally able to add the below equation to the % Complete column. By changing this dependency setting, it does eliminate the progress bar from appearing in the gantt view though. Just an FYI
Equation to use:
=IF(Status@row = "Complete", "100%")
Answers

I think I have found a solution however, my sheet won't allow a formula to be entered in the %Complete. I can type it in, but it's as if Smartsheet isn't recognizing that I'm inputting a formula.
Here's the formula I found in the Discussions:
=IF(ISBLANK([% Complete]@row), "Not Started", IF([% Complete]@row < 1, "In Progress", IF([% Complete]@row = 1, "Complete")))
....
I need to know where to input this formula on my sheet (which I thought it HAD to be in the %Complete column)... help is appreciated!
Lynda

Lynda,
Inserting the formula you mentioned in the % Complete column won't work, because it references [% complete]@row, which means "the cell in this row of the % complete column."
That formula is meant to display status based on the % complete, which is manually entered. In other words, you could use it in your status column, and it would automatically update your status column based on % complete. (This is basically the opposite of what you were asking to do  you were asking to update the % complete based on the manuallyselected status.)
Will this format work for you? Otherwise, I think you'd have to add some helper columns to get it to do what you are asking.
Best,
Heather

Hi Lynda,
I recently tried to do this very thing to my sheet and also encountered the same error  % Complete column not allowing an equation. I found out that if you change "Options" from "% Complete" to "None" in the Project Settings, I was finally able to add the below equation to the % Complete column. By changing this dependency setting, it does eliminate the progress bar from appearing in the gantt view though. Just an FYI
Equation to use:
=IF(Status@row = "Complete", "100%")

@Jenna Kiehl , ah! Thanks for this information. Dang, doing one thing limits another  shoot! Oh well, good to know....
Since you replied  I have done as you suggested and am now getting a circular reference??? Any ideas?
Thanks!

Are you placing the formula in the % Complete column or the status column? It should go in the % Complete column/cell.
Circular reference error code comes up when the reference is in the formula text itself, or indirect where this formula references a cell which then references back to this cell.

Thank you both ladies for your help! I did get to a solution. I decided to go with a Helper Column because I still needed to use the %Complete bars on the Gantt chart. Hopefully, my users will not think I'm nutz... I named my Helper Column %Complete and renamed the "original %Complete" to Blocked Admin Use Only and then locked the Column. Hopefully, that works!

Thanks for all the suggestions. I have found that using the Workflow has enabled me to update the % without losing any functionality.
Trigger:
When rows are changed AND when 'Status' changes to 'Complete'
Actions:
Change a cell
Help Article Resources
Categories
Check out the Formula Handbook template!