Relationship between the "Status" column and other columns

quimbee

I'm relatively new to Smartsheet, but it seems strange that the Status column appears not to have any relationship to other columns in the project sheet. But maybe it's possible to use a formula somehow to do that? Some specific questions:

• If I change a task from Not Started to In Progress, can I set up Smartsheet to automatically update the Start date for that task?
• Or if I change a task from In Progress to Complete, can I set it up so that Smartsheet automatically updates the Finish date for that task?
• What about establishing a relationship between Status and % Complete? For example, when I change the Status to Complete, can I set it up so that % Complete is automatically updated to 100%?

Please see attached screenshot.

Any help would be greatly appreciated. Thank you in advance! 

Shaine Greenwood

Hello—

Normally, you'll need to build a formula in the column that you wish to change based on your status: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

However, if you're using the % complete column with dependencies, formulas can't be used in that column, as there are already calculations being performed on the backend.

You can check to see if you have the dependencies functionality enabled by right-clicking any column header and selecting Edit Project Settings.

Mike Wilday

You can use a formula in the %complete column... but if a person were to manually enter a percentage it would overwrite your formula and the formula would be a moot point.

Shaine Greenwood

Mike—

You can only put a formula in a % complete column if it's NOT being used for dependencies, otherwise an apostrophe is automatically placed in front of the = sign and it's converted into text.

Mike Wilday

Yep - I was adding to your previous post that if there no dependencies, then the formula would be a moot point if someone input their own percentage. ALSO its important to note that formulas in reports are not editable, so users of reports wouldn't be able to update that column if there was a formula in there. 

J. Craig Williams

quimbee,

To your "it seems strange that the Status column appears not to have any relationship to other columns" point ...

Part of the problem is that no one can agree on the solution so most tools are flexible.

You may want to change the Status and have something else happen (like changing the Start Date). Some people may think that the Status should change to In Progress when the Start Date arrives. Other people think both of those may not always be true.

I prefer to view Status as a result, not the cause. Status is Complete because [% Complete] is 100%, not the other way around.

The system can be set up to your (and/or your PMO's) way of seeing the world.

There are even work-around for the inability to add formulas into the [% Complete] column. I have built systems that work as you describe above .

Craig