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.

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?