Successors

Magnus Saastad

Hi,

I'm struggling with my formula designed to find Successors for each row based on the predecessors column. I've almost got it working, but as you can see from the attached photo, in cells using the formula, rows that include one of the rows are also added. For example, on row 3 (successors), 31, 32 and 33 are included, although their predecessors are 30, 31 and 32. So wherever it finds the row number included in the predecessors, it adds it to my successor column.

I use this formula to set up my successors:

=JOIN(COLLECT([Row Number]:[Row Number], Predecessors:Predecessors, FIND([Row Number]@row, @cell) > 0), ", ")

What I need to figure out, is how I can make sure it only finds rows with the exact row number in it's predecessors. I've gotten quite close, but nothing seems to work. I had an idea to use a help column where I list the predecessors differently ( 1, 2, 3 --> [1 2 3] ). This way I could set up it up slightly differently to specify the exact row, but it's not dynamic enough to work in all situations.

I used this formula for that:

=JOIN(COLLECT([Row Number]:[Row Number], predHelp:predHelp, FIND(" " + [Row Number]@row + " ", @cell) > 0), ", ")

I could then use FIND() to specify if there's a space before and after, a symbol "[" or "]" or a mix of them. I can't, however, manage to find a way to make it work in a way where it includes all four possibilities and JOIN() them in the successors column.

So I'm asking one of the following questions:

Is there any way to use a formula with 4 different variations and include all results without also adding 4 extra help columns?

Is there a better way to set up my first formula where it doesn't include rows where predecessors include the number I'm searching for (row 3 finds rows where row 30 is a predecessor?

Thank you for any help! :)

L_123

Try wrapping your row reference in a letter

A1A

For example.

(quick drop down formula for rows 2 and beyond would be

="A"+(count([row number]$1:[row number]1)+1)+"A"

Then you can wrap your result in a substitute

=substitute(JOIN(COLLECT([Row Number]:[Row Number], Predecessors:Predecessors, FIND([Row Number]@row, @cell) > 0), ", "),"A","")

L_123

Alternatively you can just add a postscript comma to the row number and then you have a definite value to search for...

Edward R.

Not sure when it was added...but here is the silver bullet:

=JOIN(SUCCESSORS([Task Name]@row), ", ")

joemc

https://community.smartsheet.com/discussion/comment/307367#Comment_307367

this is the silver bullet, thanks a lot Edward!