Dependency Logic
I wonder:
Is 'Row A' predecessor to any other?
Which rows?
Is there a formula that will accomplish this?
J
Comments

Are you able to provide more detail as to what you are trying to accomplish?

Sure.
I have a schedule. On each row I want to create a formula column that answers the questions:
1. Is this task a predecessor to any other task? (Y/N)
2. What is this task a direct predecessor to?
3. Are all predecessors to this task checked as Done? (Y/N)
Thanks,
J

1. =IF(NOT(ISBLANK(Predecessors@row)), "Y", "N")
2. This could either be really simple or really complicated based on a few things. Are there multiple predecessors? Do you have lag/lead time? Do you specify Start to Finish/Finish to Start/Start to Start/Finish to Finish?
3. Complexity of this directly correlates to the complexity of #2 along with how you would want a specific situation (if necessary) handled.
Three rows have a predecessor of row 7. Would you want all three to be checked as "ready" when row 7 is complete or just one? If just one, do you have a priority established?
Also may need some type of unique row id established depending on how everything works out.

Paul
1. The proposed formula will answer the question: "Does this task have predecessors?'. The real challenge is to answer the question: "Is this task a predecessor to any other task?". Maybe a formula that searches the entire range and adds every time the row number appears in the predecessor column of another task will work.
2. There are multiple predecessors with lags & leads and different relationships SS,SF and FF.
3. When all of a task's predecessors have the status 'complete' or the 'Done' checkbox is checked the formula would indicate that all predecessors are complete.
I appreciate your help. I can work out the formula. The real essence of my inquiry is how to generally get a variety of predecessors in a formula and I plan to write this out with the formulas I create when we have it figured out to be a resource to anyone.
Are we essentially going to be parsing text for row numbers and building logic from that? I'm hoping there is a more eloquent solution.
All task have a unique ID. Searching for a column which contains the row# as a predecessor won't work because of the low range. (Searching all predecessor columns for row '50' will return results for '150','250','350'..... Maybe there's a way to do it, like filling Row 199 with null.
J

Paul
1. The proposed formula will answer the question: "Does this task have predecessors?'. The real challenge is to answer the question: "Is this task a predecessor to any other task?". Maybe a formula that searches the entire range and adds every time the row number appears in the predecessor column of another task will work.
My apologies. I misread your original question. Looking for the row number itself will have the same drawback as what you mentioned below where "50" will be triggered by "150", "250", etc. We would need to find a way to reference the unique row id's to be able to accurately pull this information.
.
2. There are multiple predecessors with lags & leads and different relationships SS,SF and FF.
This can get really ugly, really fast.
.
3. When all of a task's predecessors have the status 'complete' or the 'Done' checkbox is checked the formula would indicate that all predecessors are complete.
Got it.
.
I appreciate your help. I can work out the formula. The real essence of my inquiry is how to generally get a variety of predecessors in a formula and I plan to write this out with the formulas I create when we have it figured out to be a resource to anyone.
Are we essentially going to be parsing text for row numbers and building logic from that? I'm hoping there is a more eloquent solution.
Unfortunately... Parsing is the best solution.
.
All task have a unique ID. Searching for a column which contains the row# as a predecessor won't work because of the low range. (Searching all predecessor columns for row '50' will return results for '150','250','350'..... Maybe there's a way to do it, like filling Row 199 with null.
J
.
The solution I am leaning towards (just an idea at this point) is parsing out your predecessors and leveraging that to determine the predecessors' unique row id's. We would also need to build additional logic to account for lag/lead time (not too terrible) and your SS/SF/FF relationships (not sure on the details of how to accomplish this portion).
I am thinking PLENTY of helper columns to parse out each detail of each predecessor. Once we get it all broken down, the logic shouldn't be too hard to work out. It's the parsing that is a pain.
How exactly is your unique row id established? How much flexibility do you have with this?

I was hoping this wasn't the case. I will procrastinate on this for a long while. Let you know when I come up with something.

@Paul Newcome FYI, I did solve this & call it "Smart Successors" 😁
We just used the API to do it.
Thanks for your help.

Unfortunately I don't have much knowledge when it comes to the API, but I'm glad you were able to find a working solution.

@JeremiahHorstick we are encountering a similar requirement. Would you be willing to share your code or point me in the right direction? Thanks!

@Jon Weiss You can look me up and contact me directly. I'm the only Jeremiah Horstick in the world. 😉
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives