De-Scoping/Deactivating Rows

brenttopa

We need a function where users can de-scope rows, thus deactivating that row and removing its influence on its parent rows.  Is that an option via Smartsheet?  

Andrée Starå

Hi Brent,

Yes, it is! One way could be to have a check-box column that if checked it won't be included in the parent calculation. A formula would decide if it should be included or not. 

Would that work?

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

brenttopa

That would be perfect! I was already thinking a checkbox function. What would that formula look like?

Paul Newcome

One thing to keep in mind with this...

 

If dependencies are enabled and the parent rows are automatically a rollup of the child rows, checking the box will NOT remove it from the calculations.

 

This will only work if the dependency settings are turned off, and you are using your own formulas in the parent rows.

brenttopa

Thanks Paul.  That's a great point.  Fortunately we don't have too many dependencies but I will make a note for our presentation when this feature goes live.

Paul Newcome

Happy to help. 

brenttopa

Hi Andrée, I've set up the checkbox functionality and conditional formatting to mark the row as "Descoped" once a user enters notes in the appropriate column.

 

However, I have no idea where to start on the formula to remove the row from its respective parent once the status is "Descoped".  Would you please help men on that?  Thank you!

Andrée Starå

Happy to help!

Try something like this.

In this example, you'd add the formula to the parent where the values you'd want to sum is.

=SUMIF(CHILDREN(Status@row); "Descoped"; CHILDREN())

The same version but with the below changes for your and others convenience.

=SUMIF(CHILDREN(Status@row), "Descoped", CHILDREN())

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

brenttopa

Here are two screenshots of our current sheet.  Screenshot #1 is our sheet before descoping.

 

When users want to mark a row as "Descoped", they must enter text in "Descoped Notes".  When they do, a hidden checkbox cell will become checked. The row will then turn gray and the health ball will disappear (see screenshot #2).

 

Ideally, when the row is descoped, I would like the row's date and % complete information to be pulled out of its parent row roll-up so that it is not calculated.

 

Please note that I currently have dependencies enabled on this sheet.

Andrée Starå

As Paul mentioned it will only work if you don't need dependencies.

Do you need to use it?

Paul Newcome

Dependencies CAN be worked around, but depending on your needs it can get rather complicated rather quickly to replace the automation with your own. Working in multiple blackout periods for holidays, weekends if your durations are anything different than a week, predecessors, lag/lead time, etc... It can all be built in, but each aspect adds in layers upon layers of complexity.

 

For example... My project plans are about 650 rows each. The duration of each task has been set to weeks (1 week, 2 weeks, 3 weeks, etc.) which fortunately works rather well for what we do here. I have a 2 week blackout period for Christmas/New Year holiday. 1 week for Thanksgiving, and a blackout period for specific deliverables from the 15th of December to the 15th of January along with taking into account putting the project on hold.

 

We have 12 columns for display (things such as task name, owner, assigned to, needed or not, risk, duration, etc.).

 

But then it took 93 hidden "helper" columns to replace the automation of the dependencies in order to take into account all of the blackouts and holidays and whatnot. And that's with the easy duration of weeks for everything.

 

So... It is possible, and it could be relatively straightforward. It just depends on your needs which drives the complexity.

Andrée Starå

Yes, of course. I meant in a more general sense and the built-in feature. 

93 helpers, that's nothing. I think I used something like 160 at one point 

Paul Newcome

160?!? My sheets are getting obnoxiously slow already! You must be running on a much better computer than I am. Hahaha

brenttopa

You're right.  I apologize.  I'm still new to Smartsheet and trying to understand everything.

 

Since I'm so new, it would be easier to keep dependencies on.  I have the automation established to mark rows as "Descoped" based on a hidden checkbox column.  Since we don't have a ton of descoping to do, I will just manually move tasks to a separate sheet when descoped.  We don't want to delete them permanently but also don't want them rolled up into the parent rows.

 

I appreciate both of you looking into this for me!

Andrée Starå

No need to apologize. We’re here to help!

Because it’s ok to move the row you could maybe use the third-party service, Zapier to automate it.

Would that work?

Paul Newcome

If you are moving rows, but don't want to delete them permanently...

 

You could create a new parent row that is separate from the rest of the sheet (but still on the same sheet) and move descoped rows to be children of that parent row. That way you maintain the visibility of "we have it but don't need it for this project", and it will keep it separate from the other actual deliverables.