Options
✭✭

Hello everyone,

is there a way to set the % complete based on the task status?

I have a table

[state ; % complete]

I tried with "VLOOKUP" function but isn't possible to insert formulas in the field.

Thanks

• ✭✭✭✭✭✭
Options

Hi @malanto,

This is possible, but you would need to define the criteria.

You would use a nested IF statement. A very basic example:

Status % Complete

Not Started 0

In Progress 50

Complete 100

Would have the following formula in the % Complete column:

=IF([Status]@row="Not Started",0,IF([Status]@row="In Progress",0.5,IF([Status]@row="Complete",1,"")))

This assumes you've set the column to be %, otherwise you'd want to increase the numbers by 100 (0.5 to 50).

Hopefully this gives you some ideas, but if you're still unsure or have any problems/questions then just ask! 🙂

• ✭✭✭✭✭✭
Options

Hi @malanto,

This is possible, but you would need to define the criteria.

You would use a nested IF statement. A very basic example:

Status % Complete

Not Started 0

In Progress 50

Complete 100

Would have the following formula in the % Complete column:

=IF([Status]@row="Not Started",0,IF([Status]@row="In Progress",0.5,IF([Status]@row="Complete",1,"")))

This assumes you've set the column to be %, otherwise you'd want to increase the numbers by 100 (0.5 to 50).

Hopefully this gives you some ideas, but if you're still unsure or have any problems/questions then just ask! 🙂

• ✭✭
Options

Hi @Nick Korna ,

thank you.🤗

I had thought about creating a table [status ; % complete] in a separate sheet and using the VLOOKUP function.

If I change the % to a status, thanks to the separate table I don't have to change the formula in all the sheets.

I couldn't insert the formula because I was trying to do it on a line that contains secondary activities.😑

Now I understand how to do it.😊

Thank you ! you are fantastic ! 😃

Have a nice day!

• ✭✭
Options

I tried to enter the formula in the field but unfortunately I can't get the desired result.

In the project settings I set % Complete column = % complete

When I try to insert a formula into one of the fields of "% complete" column, the formula is not recognized@Nick Korna

Thank you!

• ✭✭✭✭✭✭
Options

Are you getting an error message when the formula is entered?

• ✭✭
Options

No nothing.

The formula appears as normal text.

While I was typing or pasting the formula I noticed that at the beginning there is a ' symbol which I didn't insert and which I can't delete.

• ✭✭✭✭✭✭
Options

The ' is what is causing the formula to appear as text, but I'm not sure what makes this happen even if you're typing the formula in. 🤔

• ✭✭
Options

Could it be because I set that column as % complete column in the project parameters?

I need this so that the "PARENTS" fields are filled in correctly way

Thank you.

• ✭✭✭✭✭✭
Options

I've not seen something like that before - are you using some premium addons (which I don't have)?

• ✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!