Custom Status Column Update based on Tasks complete

bgilbert

My project sheet is very granular. I run 50+ sheets simultaneously, and have a metadata sheet with all key information linked for at a glance progress updates for the various milestones for each project.

To give further detail on the at a glance page, I would like to give a "status" update for each parent status cell. This will require an individual formula for each unique phase of the project, but I don't know what the formula should look like. Below is what I would like the status to look like.

For the 50% LV Status cell, I would like it to update based on what is marked complete, with what step is in progress.

For example, if Line 51 status is marked complete, the parent row, line 50 would update to Received; When line 52 (and 51) is marked complete, status for row 50 would change to Distributed; Line 53 is complete, (and 51, 52) Line 50 status updates to Reviewed; line 54 would change line 50 to Sent for Approval, line 55 would change line 50 to Approved, line 56 would change line 50 to Call scheduled. When all status's are marked complete, the Status in line 50 would change to COMPLETE

Is this even possible. Relatively new to Smartsheet, but I think if I had a template formula, I could duplicate it for each unique set of tasks. Then save my template for all future projects (that run off the same sheet template)

Stefan

Hi @bgilbert,

looks like a case for a very nested IF statement.

Like:

=if(Status51 = "completed", "Received", if(and(Status51 = "completed", Status52 = "completed"), "Distributed"), if(.......

As this looks like a serial process, you also could COUNT the "Completed" status in the child rows and e.g. result = 1 => "Received" and so on for line 50.

Does this help?

Greetings

Stefan

bgilbert

Thank you Stefan,

Currently the Status gets stuck at the first "completed" task, so it stays at "Received". Would reversing the order fix this?

bgilbert

Reversing the order worked!

=IF(AND(Status51 = "complete", Status52 = "complete", Status53 = "complete"), "Reviewed", IF(AND(Status51 = "complete", Status52 = "complete"), "Distributed", IF(Status51 = "complete", "Received", "")))

Follow up: Is there a way to include if status is "complete, or NA" instead of just complete?

Stefan

Hi @bgilbert,

nested IFs are tricky on the logical side; sorry I didn't mention that. But that is the reason for the other suggestion with just counting. Will only work in strictly serial procedures.

If you want to stick with the IFs, I personally found it helpful to draw the logical path on paper before coding it ;-)

Hope this helps!

Stefan