Reference Predecessor Cell / INDIRECT Function

Justin L

I'm using a formula to automate a status column that updates based on dates and/or a "Complete" checkbox.

The formula has to reference the task before and I've gotten that to work just fine if it's the literal task before.

My problem is that I'd like to adjust the formula so that it references a predecessor task (as indicated in the Predecessor column) rather than just the first task before.

For example, part of the formula contains if "Complete6" is checked. Rather than indicating explicitly in the formula "6", I'd like it to join together "Complete" with the number in the predecessor column. So if the formula is in row 10 and the predecessor is row 5, it changes to "Complete5" accordingly.

I'm thinking a helper of some sort is needed however I can't quite figure that part out. In other sheets, I could simple have a helper column and use the INDIRECT function such as:

INDIRECT("Complete" + Predecessor@row)

TL;DR - I'd like to create the formula similar to INDIRECT.

---

Formula that works for cell/task directly before:

=IF(Complete@row = true, "Complete", IF(ISBLANK(Finish@row), "", IF(Finish@row < TODAY(), "Late", IF(AND(Complete6 = true, Finish@row >= TODAY()), "In Progress", "Not Started"))))

How can I adjust this to essentially become "Complete" + Predecessor Row?

Paul Newcome

Do all of your rows only contain a single predecessor? Do you have any lag/lead time built in? Do any of them display "SS", "SF", "FF", or "FS"?

Justin L

@Paul Newcome at the moment no, no lag or lead time. and mostly all single predecessors.

let's assume the simplest first where there is no lag/lead time and no multiple predecessors.

Paul Newcome

Ok. Smartsheet just rolled out a new function SUCCESSORS. I have not had any time to play with it yet, but her is the help article on it:

https://help.smartsheet.com/function/successors

This may be part of the solution for you.

Justin L

Good to know. Thanks @Paul Newcome!

Paul Newcome

Happy to help. 👍️

Let me know if you are able to get something working. I still haven't had a chance to play with it yet.

Justin L

@Paul Newcome so, I feel like I'd still need some helper columns even with this.

I can't seem to find a use for this function outside of the example it gives, using JOIN. And in that example, its only purpose is to show which rows depend on that row being complete.

In theory, this is kind of what I'm looking for, but I can't find a practical way to implement it.

Also, can't find a practical use for SUCCESSORS in general aside from something like JOIN. I can't seem to use it by itself or reference anything within just that.

Paul Newcome

Ok. I should have some time this week to have a play with the function. Things have been crazy, so I haven't had a chance to really experiment with anything. I will get back to you as soon as I figure something out whether it be a solution or that it is just not possible.

Paul Newcome

I think I have a solution for you assuming you are not using lag/lead time, "SS"/"SF"/etc., or elapsed time. It does allow for multiple predecessors. It is a little bit long winded, so here is a link to it:

https://community.smartsheet.com/discussion/84257/successors-testing