# Dynamic Status

Options
✭✭✭✭✭
edited 12/09/19

A more refined and slightly more universal solution to the first posting is as follows:

=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE", IF([% Complete]18 > 1, "INVALID ENTRY", IF([% Complete]18 < 0, "INVALID ENTRY")))))

This assumes that "% Complete" is calculated from something like "=[Task Hours Completed]18/[Task Total Hours]18"

If you are entering the "% Complete" column manually, be sure you enter your value as a decimal value between 0 and 1 and make your column a % column by selecting the column and clicking "%" in the tool bar. The above formula will work, but the following formula is all you will need:

=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE")))

This formula will enter:

"NOT STARTED" if % Complete = 0,

"IN PROGRESS" if 0 < % Complete < 1,

"COMPLETE" if % Complete = 1 and

"INVALID ENTRY" if % Complete < 0 or if % Complete > 1.

Note on conditional formatting:

If you adjust the % values such that you have at least one of the 4 different results showing in your column somewhere ("COMPLETE", "IN PROGRESS", "NOT STARTED", "INVALID ENTRY"), then you can add rules to conditionally format the "Status" column such that, if Status is "Complete" then make the background green and the font black. I added a screenshot of my rules.

I hope this helps someone, somewhere.

I am curious if anyone will use this. If you do use it, let me know by commenting, or by clicking the heart. This is purely for my own edification.

• ✭✭✭✭✭✭
Options

Hi Ben,

Nicely done!

Thanks for sharing!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭
Options

A more refined solution is as follows:

=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE", IF([% Complete]18 > 1, "INVALID ENTRY", IF([% Complete]18 < 0, "INVALID ENTRY")))))

This assumes that "% Complete" is calculated from something like "=[Task Hours Completed]18/[Task Total Hours]18"

The conditional formatting is still the same as I described above.

I'm curious if anyone will use this. If you do use it, let me know by responding here, or clicking the heart. Thanks.

• ✭✭✭✭✭✭
Options

To refine it even further you could use the @row function so you wouldn't need to think about row numbers.

=IF([% Complete]@row = 0, "NOT STARTED", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), " IN PROGRESS", IF([% Complete]@row = 1, "COMPLETE", IF([% Complete]@row > 1, "INVALID ENTRY", IF([% Complete]@row < 0, "INVALID ENTRY")))))

Hope that helps!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!