Predecessors "tolerant" to deletion

Ian Schumann

I'm trying to figure out if there's a way to set up our projects so that some very specific tasks, which may normally be predecessors to other tasks, can be DELETED without resulting in an ugly "#REF" error on the subsequent row.

 

We would want this because we have several different "tiers" of projects depending on the complexity of the package that our customers purchase. All tiers have most of their tasks in common, but the higher tiers have some additional tasks and dependencies that the lower tiers do not. So far we've been using a separate project template for each tier level, however this has become difficult to maintain bc we need to make parallel updates to 3 or 4 separate templates, every time we make improvements.

 

So now we'd like to merge these all into one Smartsheet project template. When we first "Save As New" the sheet would have all the complexity and added rows from our highest tier level, but we would then "prune it down" to match the tier for the customer at hand.

 

The problem with this "pruning" is that those predecessors that go missing are going to result in an irritating #REF error that needs to be deleted by hand. What we want is a way for those #REF errors to simply go away smoothly without any manual intervention.

 

Example:

Here are some tasks or rows in our project before we do this "pruning":

1) Task Group Introduction
2) IT Component 1
3) IT Component 2
4) Task Group Finish (predecessors: IT Component 1, IT Component 2)

 

Let's say the above is appropriate for our higher-tier packages but the simpler projects do not include IT Component 2. So we delete that row, and here's what happens:

1) Task Group Introduction
2) IT Component 1
3) Task Group Finish (predecessors: IT Component 1, #REF)

 

This is a small problem if it's just 1 or 2 rows, but we're talking about maybe a dozen per project, and our team handles hundreds of of projects a year.

 

We are open to setting up this unified template in any clever way that might avoid the #REF error noted in the second list above. Any ideas on how we can work around this and allow for more seamless "pruning"? I looked into using formulae for predecessors but it seems this approach would not be smart enough to tolerate our (occasionally) reordering a few rows in our projects.

 

Thanks for whatever help you can provide!

Steve Johnson

+1

Ian Schumann

I think I've figured out a workaround. It's not particularly convenient, but it's not going to be awful once we get it rigged up.

 

1) Durations for all tasks will be formula based

2) All those formulae will watch one cell at the top of the project for certain keywords which indicate our project tier level

3) Formulae will "tune" durations for all tasks based on the keyword we put into the master "watched" cell at the top of the project

4) Certain task durations will drop to ZERO when certain keywords are used

 

So for us when we provision a new project, we will simply #1 enter the correct keyword in the master cell at the top, and then #2 filter on the Duration column in order to hide all rows that now have a new duration of zero.

 

In other words, we are avoiding the deletion issue entirely. All projects for all our tiers will retain all rows and all dependencies. Projects for lower tiers will simply get around those unneeded rows #1 by marking them as zero duration and #2 then hiding them from view using a filter on the Duration column.

 

I think this will work. Any feedback is welcomed though. Or if you have a better idea, please share!

Ian Schumann

... Welp ... looks like formulae will not work in a Duration column so that is a non-solution.

John Sauber

Find and replace (CTRL+F), and be careful about it! I didn't fully vet this solution, but I do know find and replace works in the predecessor column. You'll have to test this out on your own and if you do, please write back as to how it worked.

 

1. Find "#REF, " and replace with "" to remove all errors at the begining of cells with multiple predecessors.

 

2. Find ", #REF" and replace with "" to remove all errors in the middle or at the end of cells with multiple predecessors.

 

3. Find "#REF" and replace with "" to remove all errors in cells that contain only one predecessor that's an error.

 

If you have rows with multiple predecessors, the order here will be important. Test it out and see for yourself.