Update a Status Column when % Complete

I have two columns in my Project Sheet which should always be in sync. I want my team to only update the "% Complete" column as they make progress on tasks, but I would like to see the "Status" column update automatically.

- If % Complete is = 0%, then Status should be "Not Started"

- If % Complete is > 0% AND < 100%, then Status should be "In Process"

- If % Complete is = 100%, then Status should be "Completed"

Would you help me define this?

Find more posts tagged with

Comments

Terri Pavlovic

You can use an IF statement in the "Status" column, such as:

IF([% Complete]1 = 0, "Not Started", IF([% Complete]1 = 1, "Complete", "In Progress"))

Travis

Try this:

=IF([% Complete]6 = 0, "Not Started", IF(AND([% Complete]6 > 0, [% Complete]6 < 1), "In Progress", IF([% Complete]6 = 1, "Completed")))

Stan Adell

I just saw this response after created a discussion asking the same thing.

Questions:

If someone manually enters the status it overrides the formula. Will locking the column prevent this?

How can you apply the formula to a long column with multiple parent tasks?

Travis

Hi Stan - Locking columns will prevent Editors from changing the value in the locked column. Admins and the sheet Owner can still edit locked columns.

For your second question - are you asking how to copy this formula down an entire column? You can drag fill the formula by clicking and dragging the bottom right of the formula cell or use the Copy Down keyboard shortcut. Highlight the range of cells you want to copy the formula to, with the first cell in the range containing the formula. Press CTRL + D to copy down the formula.

gnarsted

What would my formula look like if I wanted to do the reverse of this? For example:

If Status = "Complete" then % Complete is 100%

If Status = "Not Started" then % Complete is 0%

JVaihinger

gnarsted,

Formula would look like this:

=IF([Status]@row = "Complete", "100%", (IF([Status]@row = "Not Started", "0%", "")))

Currently this is set to return a blank cell if the Status does not meet any of these criteria. If you want to change this, simply add whatever you would like into the last section which is currently , "")))

Also the @row instead of calling out the row number allows you to copy paste through multiple sections without having to rework the formula.

latsyrcg3

I am using the card view as a Kanban board. When someone drags and drops a card into the completed lane, I want the Completed Date to update with the date the status changed to "completed."

I don't want to lose the drag and drop functionality because that is what makes it so user friendly (if they have to enter the date, they would have to open the card and edit it. However, knowing the date something was completed is important for reporting.

I am open to other suggestions!