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! :)



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!