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