Formula for: %Complete to be automatically changed to 100% when Status is updated to Complete

Lynda Ross

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!

Jenna Kiehl

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%")

Lynda Ross

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

Heather Duff

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

Jenna Kiehl

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%")

Lynda Ross

@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!

Jenna Kiehl

@Lynda Ross

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.

Lynda Ross

@Jenna Kiehl @Heather D

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!

PeteSage

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