How to determine if a row id is in the list of predecessors for any row
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.
Comments
-
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.
-
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!
-
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
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!
-
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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!