Define Predecessors with Something Other than Row Number

Options

Is there a way / formula that could be used to define a predecessor based on a value in another column, rather than having to look up the row number?

For example, each row in my sheet has a unique ID, and I want to be able to say the row(s) that match a certain list of ID numbers are predecessors to the row I'm in.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @BSADK

    There currently isn't a way to input a unique identifier, such as an ID number, to have your sheet automatically identify what row should be the Predecessor for that specific task. Project Sheets require the Predecessor relationship to be defined by the row number in the Predecessor column. (See: Enable Dependencies and Use Predecessors)

    That said, if you're looking to pull the ID number after you've defined the row relationship, you could potentially do this with an INDEX(MATCH formula which would search for the row number you input in the Predecessor column and return the corresponding ID from that row. Here's another Community Post with an example of this type of solution.

    Cheers,

    Genevieve

  • BSADK
    BSADK ✭✭✭
    edited 02/14/22
    Options

    @Genevieve P.

    Thank you for the reply. I see how I can do the reverse lookup. Ultimately, Smartsheet will not allow a formula in the Predecessor column then, correct? I figured I couldn't just enter the ID in Predecessor, but was hoping I could formulaically get the row number based on ID and enter that into the Predecessor column, but I see that doesn't work as everything in Predecessor is entered as text, even if I start with "=". That's unfortunate.

    On the reverse lookup - it's pretty straightforward how to do it for a single predecessor/item, but is there a way to do it for an arbitrary number? One task might have 2 or 3 predecessors and I would need to have a comma separated list of each of those predecessor IDs.

    Also, given no built in option to do it - this is further down the line, but would it be possible to populate the ID column via a WebApp using the API?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @BSADK

    You are correct: formula use is restricted in the Predecessors column, this detail will need to be manually input (see: Areas where formula use is restricted).

    I personally haven't been able to retrieve details if the current row has more than one Predecessor defined; you could use the LEFT or MID or FIND functions to extract separate row numbers, but as soon as you have more than 2 this then wouldn't work as you'd have more than one comma to find between row numbers. There may be other solutions here in the Community, but you're correct that the most simple solution is for 1 Predecessor at a time.

    You can use an Update Row action via the API to set a Predecessor object.

    Please also provide your feedback to our Product team on how you would like to see Predecessors defined in Smartsheet!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!