Is there a formula that will help me to determine if a task has any dependencies (IE: the opposite o

Dan Brenner PMP

I work on some pretty complex projects and the templates we use have hundreds of tasks that are linked together using the predecessor column.  Each project is slightly different and we usually need to delete certain tasks to refine the template into a new project plan.

 

It can be a pretty big pain to delete some of the tasks because I don't have an easy way to see if the task has anything as a dependency and, after I delete the task, whatever had it as a predecessor will now show #REF.

 

This leaves me scrambling to figure out which task it used to be linked to and attempting to reassign the task to something else that might not make the most sense.

 

My ideal solution would be a column that told me the opposite of what the predessor column shows now... but I would settle for a function that told me if the task I was looking at had any dependencies attached to it at all.

J. Craig Williams

Dan,

 

Do you have a maximum number of predecessors per tasks?

 

Craig

Dan Brenner PMP

After a quick scan of the template, it looks like 4 predecessors is the max count

J. Craig Williams

This would be so easy if I had a "match" function.

 

Four is not too bad. I was hoping for two.

 

I assume you also have FS, SF, SS, FF predecessors?

And lags and leads?

 

The simplest solution would to copy the predecessor column to a text file.

I could write an awk script in about an hour (maybe less because Smartsheet nicely adds some convenient spaces), taking into account the predecessor types and leads/lags.

 

The second simplest solution would be to have another sheet that you could copy in that copy/pasted column into - and it would flag the ones that have predecessors.

I don't know how many columns I would have, but it would not be one, I need to be able to debug it. That would take longer. Probably half a day.

 

The third solution would be to wrap all those columns from the second solution into a single column formula 'horrible and beautiful'.

That would require doing the second solution first and then wrapping it up another half a day.

 

Roughly.

 

You need to parse the text (which is what Smartsheet is doing behind the scenes already). Find the commas, substitute out the leads, lags, and predecessor types

 

Could be fun.

 

Craig

 

 

Dan Brenner PMP

As much as I hate the idea of managing a project in excel, if this were in excel I would use something to this effect to search the predecessor column for the task number and if there were any rows that had that number in them, it would return "Dependent's detected" 

 

=IF(ISNUMBER(SEARCH([@[TASK#]],[Predecessors])),"Dependents Detected", "No Dependents")

 

Not sure how closely this function would translate into smartsheets though...

J. Craig Williams

The first idea to solve the 'why is this so damn slow' problem is to cut down to 2 predecessors with a flag that there are more than 2.

This would likely cut 20% or more of the hits. 

 

It would be nice if 

 

=IF(false,here-is-the-really-long-and-cpu-intensive-calcs,do-nothing)

 

would allow us to disable all the processing, but I think it only makes it worse.

I'll test that next week - no more free time this one.

 

Craig

Steve Johnson

What you seek is a "Successors" column.  Unfortunately, Smartsheet has not added this essential feature yet.  Its omission is maddening.

Dan Brenner PMP

J.Craig is a BEAST!! 

 

Entering "4FS +1d" into row 6 seems to break everything for some reason, but I've tested most other scenarios and it works great!

 

How do I get/use this masterful code you've stiched together for me?

J. Craig Williams

Dan, 

 

Shoot me an email: jcwill23@gmail.com

I'm looking at the bug. Taking the sheet off line while I debug.

 

Craig

J. Craig Williams

Bug is fixed, but Smartsheet crashes when I try to save the sheet.

 

Craig

J. Craig Williams

Once again, the bug was easy to fix, but the slowness of Smartsheet is killing me.

 

Click on cell in Row 2.

Copy (Ctrl+C)

Select column for Rows 2-100

Paste.

Click on Row 100

Wait 30 seconds - Chrome gives warning is not responsive

Wait 30 seconds - 

Wait 17 seconds. 

Now obvious that cursor is now in cell on Row 100 (and not a selection of 99 rows)

Save icon shows sheet needs to saved.

 

Very many more and the Chrome message becomes a crash.

 

Here's the updated sheet:

 

https://app.smartsheet.com/b/publish?EQBCT=844c02f40fe74b528d80e1f951145113

 

J. Craig Williams

Dan,

 

Try this:

 

(update 2016-05-12  - link changed)

https://app.smartsheet.com/b/publish?EQBCT=844c02f40fe74b528d80e1f951145113

 

 

Copy in your predecessors.

Only 500 rows.

 

I couldn't sleep.

Took me 45 minutes to have a working solution with 20 rows.

The next 90 minutes have been lost because I tried to make it 1500 rows.

 

SLOW.

CRASHES WHEN SAVING.

VERY SLOW.

MORE CRASHES.

 

I cut it down to 500 rows and it still crashes when saving.

But i think it will work now.

 

Also, I'd prefer to NOT have the Gantt chart showing in the published sheet, but didn't see how to turn it off.

I considered changing the Predecessors column to Text/Number but thought that might introduce problems.

 

Give it a try and let me know if it works.

I tried several different combinations of # of pred, predecessor types, and lead/lags.

 

See if you can break it.

I'm going to try to sleep again.

Craig

 

steve brown

Not hard:  Note the line # of the item (example:  line 257 "project review") about which you want to know: "is there a a line item with a dependency on this line item 257 somewhere?".  Highlight the whole "predecessor" column and search for that number (257).  That finds every line that is dependent on your item in question.

jturanin93261

You can filter dependencies using the Predecessor column. If you want to know if any other row is a successor to a particular row, filter the Predecessor column by that row number (right click on Predecessor column header, select Filter, select Predecessor-contains-<enter row number>.

Works like a charm for a single row inquiry. Only problem is if you filter to a row with a single digit, it returns all row numbers containing that digit. But its better than nothing.

Andrée Starå

Hi,

Excellent tip!

Thanks for sharing!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting