Link % completion and Status fields

nozawa51306

We have 2 columns.

1) % completion 

2) Status (not started, in progress, completed, significant delay, etc).

 

We would like that every time we move % Completion from 0 to any number up to 100, Status gets automatically updated to "in progress". And when % completion hits 100%, then Status is "Completed"

 

We will still want to manually override when a task gets signficantly delayed.

Does anyone know how to do this?

Stefan 7k

Hi,

typical usecase for a nested IF statement.

I use this in a similar case.

=IF(AND([% done]1 > 0; [% done]1 < 100); "in progress"; IF([% done]1 = 100; "Done"; ""))

Greetings

Stefan

Paul Newcome

Also keep in mind...

 

If you manually update it, then it will remove the formula, and you will have to re-enter it if you wanted the automation to resume.

 

And the ; should also be changed to a , in the formula above. Another recommendation is changing the row reference from the row number to @row. The first is a MUST. The second is simply a recommendation.

Stefan 7k

Paul,

when your Smartsheet is set to other languages (in my case German), the separator is not , but ; !

Internally this is changed by the parser, but while entering formulas, I have to use ; .

Greetings

Stefan

Paul Newcome

Oh wow! Learn something new everyday! Thanks for the info, and I will certainly keep that in mind.

My apologies for the incorrect assumption.

Katye Reed

Hello, I am struggling to get this to work. I am trying to apply this for 3 statuses (Not started, In Progress and Completed) so I thought a nested IF statement would work. Nothing works. I even tried the formula posted above, nothing.

Any suggestions? You're help is greatly appreciated.

 

Stefan 7k

Hi Katye,

try to type the formula by yourself instead of copying.

A few times I found that copied formulas did not work in a given sheet, even though the syntax and everything was correct.

When I typed the same formula manually it worked. I guess the parser/code interpreter from smartsheet has something to do with that.

Greetings

Stefan

Paul Newcome

I've had the same issue with copy/pasting formulas from an outside source (but not often). For your specific case you will want to use:

=IF(AND([% done]1 > 0; [% done]1 < 100); "In progress"; IF([% done]1 = 100; "Complete"; "Not Started"))

If your column is formatted as an actual percentage (as opposed to text with the % manually added) you will also need to adjust your values. Smartsheet reads percentages as a decimal.

0% = 0

1% = .01

99% = .99

100% = 1

Also keep in mind Stefan's post regarding the separators used.

Katye Reed

Hi Paul,

It worked, once I used the percentage conversions per your suggestion. 

Thanks so much for your help.

Katye

 

 

Katye Reed

Hi Stefan -

 

Thank you so much! You and Paul are extremely helpful.

JasonRiley80

I made mine work with this statement

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

Shashank

Hi, I'm looking for just the reverse. if I put Status as Not started, the percentage should be 0%, if the status is assigned, then percentage should change to 20%. if status is in progress, percentage must change to 50% and lastly if the status is completed, the % must be 100%.

The reason I'm asking is, there is a separate excel sheet where I get the status information and need to convert it in % so that, I can show the project overall progress.

Genevieve P.

Hi @Shashank

You can use a formula for this, or what might be easier is to use the Change Cell Workflow in Smartsheet. You can set the workflow to change the number in the % Complete column based on what's selected in the Status column. This way you could manually adjust the % Complete in other cells, if you wanted.

Cheers,

Genevieve

Shashank

Thanks @Genevieve P. , This is really helpful and I have implemented in one of my Project.

I have few continuation questions and need to check if you could share some insight.

1. I see you have created a grouping in the screen shot, however my screen shot of workflow is separate (Ref. the print screen). may I know how I can group them?
2. When I manually run to the existing sheet, it updates the entire sheet percentages instead of the one with status change..
3. Is there a way to copy the workflows to other sheets which is already live with same status. I have implemented at template level, however is there a way to copy the workflows to the current live projects with similar status?Thanks and wish you a great day.

Genevieve P.

Hi @Shashank

1. Do you meant that I have multiple Condition Blocks in the same workflow? To get this, click on the blue PLUS button above the current Condition Block:

Here's more information: Condition blocks: Filter what your automated workflows send

2. Yes. Manually running a workflow skips your Trigger because you're running it instead. This means it will look at all of your rows. Here's more information: Manually run a workflow

3. No, there currently is no way to copy workflow set-ups between sheets. You'll need to re-create the same workflow in each of your sheets. If you're creating entirely new sheets, then you can copy this entire sheet and it will copy the workflows as well!

Cheers,
Genevieve