How to determine if a row id is in the list of predecessors for any row

Nel C

Hello, I have a fairly large Project plan with 700+ rows and am new to Smartsheet.

My predecessors list for any given task has multiple row IDs referenced (comma delimited)

With help from Rob Hagan's post (https://community.smartsheet.com/discussion/formula-determine-row-number), I have created the ROW_ID) column for reference in my formulas). 

My Ultimate goal: 

I would like to create a dependencies column in my spreadsheet. 

I'm looking for help on how to best do that. 

I was thinking of using  the Join and Collect functions, but don't know how to get it to work.   What should I put in the cirterion field? (i.e. I'm looking for help in replacing "contains Row_ID1)" with the proper formula.)

=JOIN(COLLECT([ROW_ID]1:[ROW_ID]40,[Predecessors]1:[Predecessors]40, "contains " row_id1),","))

 

where "ROW_ID"  column contains the "Row # matching the system Row ID and "Predecssors column contains the comma delimited list of predecessor referernces. 

 

 

Shaine Greenwood

Hi Nel C,

Are you using the built-in Dependency functionality? You might benefit from seeing this in the Gantt directly.

More on built in dependencies here: https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors

Otherwise, there isn't a contains or wildcard type feature in Smartsheet formulas.

Maybe with more information on what you're wanting to accomplish with the column that exists outside of the normal dependencies functionality, I (or other community members) can figure out a solution.

Nel C

Hi Shaine,  

 

Thanks for your reply. Like many others, I am trying to find a way to display the list of successor or dependent tasks on a given row. (i.e. the opposite to Predecessors).  

So, If TASK 3 and TASK 4  (on rows, 3 and 4 respectively) are dependent on TASK 1 (on Row 1), then the Successor Column on Row 1 will list "3, 4"). 

I recognize that the Gantt chart shows dependencies via the arrows but I have over 1000 rows in my sheet and there are multiple dependencies...so it becomes harder to scroll and follow the  different paths back to the successor.  - hence my desire to display it in a column.,

 

Using a couple of columns, I have managed to transform the predecessor field (which is potentially a comma de-limited field with a mixture of straight row IDS, as well as ROW IDs with leads/lags etc. ) e.g. (3,45FF,5SS +1d) -> (,3,45,5,).  

 

What I still can't get to work is the Join/Collect function to return the list of rows where cells in the criterion range have comma delimited lists.

Question:   Can I have an "if statement" in the criterion field of the Collect function?

This is what I have currently but it doesn't work:

JOIN(COLLECT(RowNum:RowNum, PREDMinusS:PREDMinusS,

@cell =IF(FIND("," + RowNum@row + ",", PREDMinusS@row) > 0, PREDMinusS@row, 0 )  )  ,")

 

where,

RowNum is the column with the ROw IDs that I want to return. 

PredMinusS is the column containing the :cleaned up, comma-deliminted list of Predecessors (i.e. without FS, FF, SS, SF) on which I can search for the Row ID. 

Problem is, as you likely know, the FIND function returns the place in the string where the search key is found, 0 if not.    However, the PREDMinusS fields contain the delimited list ...so I thought that if I wrap the FIND statement with the  "IF statement to return  the content of the PREDMinusS cell, then I would be golden....but sadly..it is not working for me.  ....is there a way to assign the if statement to"...

ANY help you can give would be much appreciated.

 

Thanks!

 

 

 

Nel C

For anyone trying to figure out how to determine a row's Dependencies (a.k.a Successors) in a sheet with hundreds  or thousands of rows), I have found a way to provide as input, the rowid to be analyzed and return the list of rows that are dependent on it. (I.e. the opposite of "Predecessors").  I understand that the Gantt Chart view will show this if you follow the arrows...but in a large sheet, this is not practical.    

Below is a link to the solution I came up with

https://community.smartsheet.com/discussion/looking-turn-predecessors-cell-comma-delimited-list-without-ff-fs-x-d-etc#comment-83186

I'm still hoping that Smartsheet will come up with a proper solution (i.e. provide a "Successors" column that auto populates as the Predecessors column is updated.

Hope it is helpful!

Udo

Hi Nel C,

just would be interested on how you actually did that:

"Using a couple of columns, I have managed to transform the predecessor field (which is potentially a comma de-limited field with a mixture of straight row IDS, as well as ROW IDs with leads/lags etc. ) e.g. (3,45FF,5SS +1d) -> (,3,45,5,).  "

Could not find a way so far.

Would be easy using "replacing with wildcards", but does not work in Smartsheet.(?)

Thanks

udo

Nel C

Hi @Udo , Sorry, I did not see your post until now. It has been some time since I actually made use of this functionality . I had documented the details of my solution via the following post: https://community.smartsheet.com/discussion/looking-turn-predecessors-cell-comma-delimited-list-without-ff-fs-x-d-etc#comment-83186

Perhaps you can take a look there to get the answers you are looking for. I noticed that others have come up with improvements as well. - maybe something even more streamlined than the clunky solution I came up with

Good luck!

Nel