Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭
    edited 06/04/24 in Formulas and Functions

    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

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    Answers

    • Community Champion
      Answer ✓

      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

      image.png
      · Share on FacebookShare on Twitter
    • ✭
      Answer ✓

      Unreal, it works perfect thank you!

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

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

      · Share on FacebookShare on Twitter
    • Community Champion

      Hi @Lindsay AR

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

      jmyzk_cloudsmart_jp

      Site faviconSmartsheet

      (Link to the published demo sheet)

      image.png

      Related Discussion

      I need help automatically changing status of all successors based on the status of a deliverable

      blawrence13
      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @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.

      · Share on FacebookShare on Twitter
    • Community Champion

      Happy to help!😁 @Lindsay AR

      · Share on FacebookShare on Twitter
    • ✭✭
      edited 02/12/25

      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)?

      · Share on FacebookShare on Twitter
    • Community Champion

      Hi @Amy Kwok-Parkhill

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

      Site faviconSmartsheet

      image.png

      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.

      · Share on FacebookShare on Twitter
    • ✭✭

      jmyzk_cloudsmart_jp

      @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!

      · Share on FacebookShare on Twitter
    • Community Champion

      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])

      image.png

      "

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions