How to use the data in the Predecessor Column as a variable.

Forrest Drake

Im trying to use the number inside of the predecessor column to call out the location of the row to get information from that row.
=VLOOKUP([Predecessors]@row, {Predecessors}, {column x}, false)
=INDEX([column x], MATCH([Predecessors]@row, [Row]))
none of these seem to work. Is there a way to call the predecessor column as a int. IE the predecessor is row 10. =[column x]10

jmyzk_cloudsmart_jp

Hi @Forrest Drake

VALUE(Predecessors@row + "") worked for me.

=IF(ISBLANK(Predecessors@row), "", INDEX([Task Name]:[Task Name], VALUE(Predecessors@row + "")))

If you test the Predecessors@row with ISNUMBER and ISTEXT, the result shows that the predecessor is neither a number nor a text. So, to use it in INDEX or VLOOKUP, I converted the value first to text by adding "" and then used the VALUE function to convert it to a number.

https://app.smartsheet.com/b/publish?EQBCT=6ab5b2a32e4542ee9417dd112fb2c90b

Forrest Drake

Unreal, it works perfect thank you!

jmyzk_cloudsmart_jp

Hi @Forrest Drake

VALUE(Predecessors@row + "") worked for me.

=IF(ISBLANK(Predecessors@row), "", INDEX([Task Name]:[Task Name], VALUE(Predecessors@row + "")))

If you test the Predecessors@row with ISNUMBER and ISTEXT, the result shows that the predecessor is neither a number nor a text. So, to use it in INDEX or VLOOKUP, I converted the value first to text by adding "" and then used the VALUE function to convert it to a number.

https://app.smartsheet.com/b/publish?EQBCT=6ab5b2a32e4542ee9417dd112fb2c90b

Forrest Drake

Unreal, it works perfect thank you!

Lindsay AR

@jmyzk_cloudsmart_jp Have you found a way to do this in a row with multiple predecessors?

jmyzk_cloudsmart_jp

Hi @Lindsay AR

Using the SUCCESSORS function is my favorite, as the function removes the Predecessors format like SS, FF, etc.

https://community.smartsheet.com/discussion/comment/439120#Comment_439120

https://app.smartsheet.com/b/publish?EQBCT=ef08e3f4a222442886203390410608ef (Link to the published demo sheet)

Related Discussion

https://community.smartsheet.com/discussion/123258/i-need-help-automatically-changing-status-of-all-successors-based-on-the-status-of-a-deliverable

Lindsay AR

@jmyzk_cloudsmart_jp

Thanks! I figured it out later in the day but that helps assure I did it similar to how others have made it work as well.

jmyzk_cloudsmart_jp

Happy to help!😁 @Lindsay AR

Amy Kwok-Parkhill

Hi @jmyzk_cloudsmart_jp, thanks so much for all your time working on this…what if my predecessors (more than 1) is quite simple, just simple numbers with a comma to denote the multiple predecessor? Is it possible to return the value of the two or three predecessors using helper columns and maintaining the dynamic nature (say a line is inserted, the predecessor value naturally changes too)?

jmyzk_cloudsmart_jp

Hi @Amy Kwok-Parkhill

Suppose we have a sheet like the image below, with some tasks having as many as four predecessors.

https://app.smartsheet.com/b/publish?EQBCT=45250f762a4140e6b4d93e35b8bd2a15

First, I added a [Pred Multi] helper multiple dropdown list column, as I want to count the number of predecessors with the COUNTM function in the next helper column, [Pred Count].

So, our task is to split and get the value from a predecessor cell, such as "10, 11, 12, 13."

To do that, we use the TEXT functions, like FIND, MID, and SUBSTITUTE. I used the FIND and MID functions to cope systematically with many predecessor cases, like 10.

Formulas

[Pred Multi] =SUBSTITUTE(Predecessors@row, ",", CHAR(10))
[Pred Count] =COUNTM([Pred Multi]@row)
[F1] =IF([Pred Count]@row > 1, FIND(",", Predecessors@row))
[F2] =IF([Pred Count]@row > 2, FIND(",", Predecessors@row, [F1]@row + 1))
[F3] =IF([Pred Count]@row > 3, FIND(",", Predecessors@row, [F2]@row + 1))
[Predecessor 1] =IF(ISBLANK([F1]@row), Predecessors@row, MID(Predecessors@row, 1, [F1]@row - 1))
[Predecessor 2] =IF([Pred Count]@row = 2, MID(Predecessors@row, [F1]@row + 1, LEN(Predecessors@row) - [F1]@row), IF([Pred Count]@row > 2, MID(Predecessors@row, [F1]@row + 1, [F2]@row - [F1]@row - 1)))
[Predecessor 3] =IF([Pred Count]@row = 3, MID(Predecessors@row, [F2]@row + 1, LEN(Predecessors@row) - [F2]@row), IF([Pred Count]@row > 3, MID(Predecessors@row, [F2]@row + 1, [F3]@row - [F2]@row - 1)))
[Predecessor 4] =IF([Pred Count]@row = 4, MID(Predecessors@row, [F3]@row + 1, LEN(Predecessors@row) - [F3]@row))

As you notice from the above formulas, the formulas have the repetitive same structures, with only specific numbers changing, like >1, >2, or F1, F2.
So, if you need to cope with a Project with some tasks having more than four predecessors, you just need to add more helper columns, only changing the numbers.

Amy Kwok-Parkhill

https://community.smartsheet.com/discussion/comment/472632#Comment_472632

@jmyzk_cloudsmart_jp : that totally works!!! So now my next step was to get the predecessor 1 task name using your original formula and I substituted instead of predecessors, predecessor 1 and it totally worked too! I'm so so happy.

since it works, everything is great, but out of curiosity, for your screenshot (and also on mine) I don't understand F2 and F3…what does it "mean"….so in your example, on line 10: F2 is 5 and F3 is 8 …what is the "5" and "8" denoting? Thanks so much in advance!

jmyzk_cloudsmart_jp

Hi @Amy Kwok-Parkhill

I'm glad it worked.

Regarding your question on what F1, F2, etc., denote, for example, in the image below, F1, F2, etc., denote the position of "," in the Predecessors cell.

10, 11, 12, 13

First '," or F1 is at position 3, as in the "10,", the "," is at the 3rd position,
second "," or F2 is at position 7, as "10, 11,", the second "," is seventh position.

The formula for [F2] below finds "," from the [F1]@row+1 or the 3rd position.

[F2] =IF([Pred Count]@row > 2, FIND(",", Predecessors@row, [F1]@row + 1))

Syntax: FIND(search_for, text_to_search, [start_position])

"