Formula help predecessor

Judith Blanken

I need a formula for

 

If there is a predecessor

the status of predecessor is complete (other row)

than show "start"

If there is NO predecessor show "start"

 

 

How can I make this?

Name of columns:

Predecessor: Voorafgaande taak

Status: Voltooid

 

THANKS!

J. Craig Williams

Judith,

 

(deleted reasons why this won't work)

 

NOTE: This only works for rows that have a single predecessor and no lead/lags.

 

1. Create a column to get your Row# [RowID]

In the first row, put a 1.

The second row, use the formula

=RowID1 +1

and copy that to all the other rows.

 

2. Create a column to get your predecess [MyPred]

The formula there is

=Predecessors1

for row 1, copy that to all rows.

 

3. I'm assuming / use a [Status] column as a checkbox.

Manually entered.

 

4. Create a column call [CheckMyPred] of type Text/Number.

 

For row 23, this is the formula

 

=IF(COUNTIFS(RowID:RowID, MyPred23, Status:Status, 1) + IF(ISBLANK(MyPred23), 1) > 0, "start")

 

pseudo code

- determine if RowID matches MyPred and Status is checked - if so, value is 1

- determine if MyPred is blank - if so, value is 1

- add the two values together

- value will be 

0 MyPred is not blank and for the row where RowID matches MyPred, status is not checked.

1 MyPred is blank OR MyPred is not blank and  row where RowID matches MyPred, status is checked.

- check if value is greater than 0 - if so text is "start"

 

 

I believe I can also get this to work for multiple predessors, even with leads and lags.

Contact me if you want that..

 

Craig

J. Craig Williams

Second note: 

You can reference the Predecessor row directly instead of going through MyPred if you only need single predecessors (of type FS)

 

Craig

Ryan vanDeventer

I've been looking for a way to do this for days.

I am having a problem though, when I Insert a new row, its not continuing the formula in the RowID Column. All of my other formulas carry down, but that one won't for some reason?

J. Craig Williams

Auto-fill is supposed to copy the formula if the two rows above it have the formula.

I've seen instances where it seems to fail, but have not found a pattern.

You might contact support@smartsheet.com if it seems like a bug.

 

Craig

 

 

mbelinkie

Craig, this is great! But I was hoping you could help me push it one step further. What I really want is a column that displays the NAME of an uncompleted Predecessor task. (To simplify this, I can make it so a given row will only have one Predecessor.) So let's say I have "Step 1" followed by "Step 2". I want a Blocker column that will display "Step 1" in the "Step 2" row, until "Step 1" is complete. 

I need this because I'm using Smartsheet to generate JIRA tickets. It would be a huge help if I could indicate which tickets are blocked and what they are blocked by. Thank you!

J. Craig Williams

Hi,

1. Move the RowID to the left of the column you want to contain the text (I'll call it TEXT in the example below)

2. Use LOOKUP() function to search the RowID:TEXT range for the value in MyPred and return column 2 (the value in the TEXT column)

Craig

 

Robert Meisch

I know this is an older thread but I was wondering if you could share your ideas regarding getting it to work with "I believe I can also get this to work for multiple predessors, even with leads and lags."

Thanks.

Robert Meisch

ChelseaH

I'm looking for a way to create a report that shows only tasks that are 'active' (Where all predecessors are complete). It was recommended to me to use a check box column with a formula that will check the box if all predecessors on that row have the 'Task Complete' box checked, but I can't figure out how to make it work. This is what I have so far:

=IF(Predecessors@row = 0, 1, IF(ISBLANK(Predecessors@row), 1, 0))

 

I schedule for multiple departments and I am daily inundated with emails telling me that a task on someone's schedule can't be done yet because the predecessor task is not complete (this is usually because the predecessor is a .5 day task) I want to be able to exclude tasks that are not ready to start. 

R Tudor

Disclaimer: This only picks up the first predecessor.

I have a column called "First Pred" which picks up the first predecessor in a predecessors list. The following is the formula for the topmost cell in this column.

=(IFERROR(LEFT(Predecessors1, FIND(",", Predecessors1, 1) - 1), Predecessors1))

I then have a different column that strips off the trailing dependency type and lag / leadtime. Below is the formula for the first cell in this column:

=VALUE(IFERROR(LEFT([First Pred]1, MAX(FIND("FF", [First Pred]1), FIND("SF", [First Pred]1), FIND("SS", [First Pred]1), FIND("FS", [First Pred]1)) - 1), [First Pred]1))

This works well, I have not found any instances where it fails. Please note, this returns a numerical value which can be plugged directly into the VLOOKUP.