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 manually-selected 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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!