Looking to turn Predecessors cell into a comma delimited list without "FF", "FS", "+x d " etc.

Nel C

Hello,

Looking for some help from skilled smartsheet formula writers on how to copy the contents of a predecessor cell into another cell but without leads, lags, "FF, FS, SF etc.". 

TO give you the full picture of what I am trying to achieve: 

My ultimate goal is to be able to identify a list of successors for a given row.  I have found via the community, and smartsheet examples,  a variety of expressions that I am hoping I could make use of to achieve this goal. 

1) The first is: 

=JOIN(COLLECT(ROW_ID:ROW_ID, PRED:PRED, =[ROW_ID]@row), ",")

where:

"ROW_ID" is the column that stores the row ID (dynamically updated)

( thanks to suggestions from Rob Hagan  - see: https://community.smartsheet.com/discussion/formula-determine-row-number  )

 and "PRED" is the predecessor column that is updated by Smartsheet as rows are inserted/deleted. 

 

The above formula only works to give me a list of successors if there is only one predecessor in the column and the predecessor is just the number (with no lead lag, or "FF, SF, SS" after it. SO I need to figure out how to replace ' =[ROW_ID]@row) ' with a search string

2)In trying to figure out how I can search a predecessor cell that is comma delimited, I came across the following  thanks to Lee T.  (see: https://community.smartsheet.com/discussion/can-successors-column-be-displayed ):

=IF(FIND("," + [ROW_ID]@row + ",", SUBSTITUTE(JOIN(PRED:PRED, " ,"), " ", "")) = 0, "", "YES")

This formula will  tell me if I Have dependencies.  My thought is that I could replace "YES", with "ROW_ID@Row" and substitute this into the the first formula. HOwever.....The  search string "," + [ROW_ID]@row + ","  only works for a clean set of predecessors without FF, FS, SF, SS, leads or lags".  (That is,  if the string returned by  SUBSTITUTE(JOIN(PRED:PRED, " ,"), " ", "") is something like "2,4FF, 6" ,  then it will not return a "Yes" if searching for Row ID 4, because 4 is followed by "FF".  I also don't believe that a search for row 2, or 6 would work as they are not preceded by or followed by a comma . The latter issue, I guess we could resolve by appending a comma at the start and end of the, but I am still left with the issue of how to strip off the "FF, SF, SS, leads and lags".  

If it were just "FF, SS, SF" after the number, I could easily strip those off because they are of consistent length...but my problem is with how to strip off the leads and lags. , where the number after the "+" could be any number of digits" and multiple characters (d, h, m, s y etc.) before we hit the comma.   (e.g. 3FS+1d, vs 3FS+15h,).

So, now my challenge is to to find a way to parse my predecessors list to just get a listing of the row ids (without the "FF", "FS", "SF", lags or leads (i.e. FS+1d).   I thought about exporting my predecessor column to Excel to cleanup and then re-import, but that will not work as I need the cleaned up list to be dynamic, as the predecessor column updates when rows are added, deleted etc. 

For example: if Row three of my predecessor column (PRED3) contains "3, 6FF, 9FS +1d",  my cleaned up column (PREDClean3) should have "3,6,9" in it.   

From what I can tell, there is no "wildcard" character that I can use or regular expressions...so not sure how I Can strip those characters of varying length (e.g. "FF", "FS+1d").

If anyone in the smartsheet community has some ideas...it would be very much appreciated! I see from the various posts that being able to list Successors is a pretty common request from fellow project managers - especially as the Project plan grows to 500+lines.  I'm really hoping that Smartsheet developers will prioritize the request that has been made by so many users over the past few years!

 

Shaine Greenwood

Hi Nel C,

This might take further parsing with some of our text functions, such as LEFT, RIGHT, and MID.

One quick example of how you can use LEFT to get a single predecessor and turn it into a value that you could use with your ROW_ID column (assuming that column only contains numbers) is as follows:

=VALUE(LEFT(Predecessors1))

If "2FF" were hypothetically in Predecessors1, the above function would return the number 2.

You can research these functions more on our functions list (switch the dropdown to Text): https://help.smartsheet.com/functions

Nel C

SOLUTION FOUND!

- While, I have not managed to find a way to determine the list of Successors on the Row itself (i.e. have a "Successors" column similar to the Predecessors column),  I have found a way to dynamically determine the list of successors for a given Row number )i.e. I have to manually enter the row ID that I am interested in analyzing) and the list will be calculated. 

Below are the instructions on how to  accomplish this (Requires multiple  columns), multiple formulas  which can be hidden once set up).   A snapshot of the sheet is also attached. 

I hope this is helpful to other smartsheet users.  It is not a perfect solution, but atleast provides a means of identifying a row's successors/dependents !

 

To Generate a list of Successors for a given Row:

STEP 1)Create  a column named "ROWNUMCounter". IN row 1 of this column, enter:

  "=1+0" 

Then drag this formula down to the end of your sheet.

STEP 2)Create a column named "ROW_ID. IN row 1 of this column, enter

 " =COUNT(ROWNUMCounter$1:ROWNUMCounter1)" 

 - Then drag this formula down to the end of your sheet. - You should see that the values in this column match the row number at the far left of your sheet.    Steps 1 and Steps2 survives addition and deletion of rows. ( i.e. auto-updates).

STEP 3) The formulas hence forth assume that  your "Predecessors" (Dependencies)" Column is called "PRED". (Column type is"Predecessors").  

To Note: Regardless of how the predecessors are entered, I have noticed that a space is added after a comma when there is a list.  I have also  noticed that if a lag or lead is added after a predecessor with FS dependencies,  the "FS" is added automatically even if it was not entered by the user.)    The formulas I use are taking advantage of this smartsheet design that is associated with a Predecessors Colulmn type.  IF this design changes, then the subsequent formulas need to be reviewed.

As an example,  we will assume that  Row 2 of the PRED column has the following:  

" 5, 23FF, 29SS, 15SF, 19FS + 1d,  14SF - 3w"

STEP  4) To the Right of the PRED Column, create the following set of columns, all being  of column type: Text/Number

• PRED-F
• PRED-S
• HasSucessors
• RowID2Find
• Successors

STEP 5) In Column "PRED-F", row 1, enter the following:

=IF(FIND("F", PRED@row) > 0, ", " + SUBSTITUTE(PRED@row, "F", ", ") + ",", ", " + PRED@row + ",")

Copy this formula down to the end of your sheet.

This removes  any "F"s that are listed in the PRED column and replaces it with a ", " (comma followed by a space).  The space is there to allow for Column wrapping should the list of predecessors be long).  In addition,  the contents of the PRED" column, are wrapped with two commas (one in the front and one in the end...so that the search  string used in "HasSuccessors" will work for all numbers, regardless of whether they are at the beginning, middle or end of the string.

Example:

 " 5, 23FF, 29SS, 15SF, 19FS + 1d,  14SF - 3w"   becomes 

 " , 5, 23, , , 29SS, 15S, , 19, S + 1d, 14S, -3w, "

STEP 6)  In Column "PRED-S", row 1, enter the following:

=IF(FIND("S", [PRED-F]@row) > 0, ", " + SUBSTITUTE([PRED-F]@row, "S", ", ") + ",", ", " + [PRED-F]@row + ",")

Copy this formula down to the end of your sheet.

This removes any "S" that are listed in the PRED column and replaces it with a "," (comma followed by a space).  The space is there to allow for Column wrapping).  In addition,  the contents of the PRED" column, are wrapped with two commas (one in the front and one in the end...so that the search  string used in "HasSuccessors" will work for all numbers, regardless of whether they are at the beginning, middle or end of the string.

Example

 " , 5, 23, , , 29SS, 15S, , 19, S + 1d, 14S, -3w, "    becomes:

 " , , 5, 23, , , 29, , , 15, , , 19, , +1d, 14, , -3w, , "

STEP 7) In Column "HasSuccesors", Row 2 and onwards,  enter the following:

=IF(ISBLANK([RowID2Find]$1), "No ROW ID identified", IF(FIND(", " + [RowID2Find]$1 + ",", [PRED-S]@row) = 0, 0, [ROW_ID]@row))

This needs to be copied down to the rest of the sheet.  oOte, that I enter this starting at row 2, as I  will be using Row 1 as an additional Row Header.  

This Formula, is first going to check if there is a value entered in "RowID2Find", Cell1.  If not,  it  returns "No ROW ID indentified".  Otherwise,  it will search for the value in the PRED-S column for that row.  IT is looking for the number to be surrounded by a comma  followed by a space on the left, and a comma on the right.  If it doesn't find the pattern in that row of the the PRED-S column, then it returns a 0, otherwise it returns the row ID. 

 This specific pattern search ( , RowID2Find, ) ensures that a false positive is not identified when the number in question appears as part of another predecessor (e.g. Looking for 1 in a row where the predecessors are (, 19, 214, 21,)  (The "leads and lags" do not matter in the PRED -S column because they will never match the pattern being searched.

STEP 8) In row 1 of  Column " ROwID2FIND",  enter the ROW ID that you are interested in finding the list of successors for.   The rest of the column's cells are not used. 

STEP 9) The last step is to collect all of the rows that have the row ID identified and display them as a comma delimited list.  To do  this: 

In Row 1 of the "Successors" column, enter the following:

=IF(ISBLANK([RowID2Find]$1), "No Row number identified", IF(SUM(COLLECT(HasSuccessors:HasSuccessors, HasSuccessors:HasSuccessors, >0)) > 0, JOIN(COLLECT(HasSuccessors:HasSuccessors, HasSuccessors:HasSuccessors, >0), ", "), "no sucessors"))

There is no need to drag this formula down to the rest of the cells in the column.

This formula is first checking to make sure that the ROwID2Find, Cell 1 is not empty.  If it is, then "No Row Number identified" is listed. If ROWID2Find$1 is populated, then, check to make sure that the "Has Successors" column has atleast one cell with a value other than 0 (i.e. the row has atleast 1 successor).  If the  Row in question, has atleast 1 successor, then  collect the list of successors and seperate them with a comma. Otherwise, enter "No Successors"

NOTES:

Unfortunately, I have not figured out how to statically list the successors for a given column.  One must dynamically enter the row ID to be analyzed in ROWID2Find$1.

I find that it is useful to have the same sheet displayed on two  windows/screens - One, where I can always see the header Row 1 and the other sheet to  search through the rows as per the list generated in Successors$1. (This basically saves me from having to scroll back and forth in a large sheet with hundresds of rows)   In doing so, one has to remember to save and refresh  in order to see changes take place as the PRED column is updated, rows are added/deleted etc.

Smartsheet Community Mentions:

In order to come up with this solution, I used the Smartsheet help, but also looked to the Smartsheet Community for assistance.  Parts of the final solution were borrowed from input provided by the following two contributors:

1) Rob Hagan for his solution on autopopulating a ROW_ID column that is updated as rows are inserted/deleted.   https://community.smartsheet.com/discussion/formula-determine-row-number 

2) Lee T. : In trying to figure out how I can search a predecessor cell that is comma delimited, I came across a formula shared by Lee T.    I used it as the base for the formula in my "Has Successors" column.  https://community.smartsheet.com/discussion/can-successors-column-be-displayed )

Nel C

IF you want to use this set of formulas to manually build up a column with Dependents, you could replace the "HasSucessors" column formula with the following:

=IF(ISBLANK([RowID2Find]$1), "No ROW ID identified", IF(FIND(", " + [RowID2Find]$1 + ",", [PRED-S]@row) = 0, 0, [UNIQUE ID]@row))

where, UNIQUE ID is a column of type "auto-number ".

You could then create another column, "Dependents" and cut/paste the output of Successors1 field as you change the row ID. (Not practical if you have hundreds of rows!).  By using the "UNIQUE ID",  your dependencies list does not become stale as rows are added/deleted.  It will, however become stale if the predecessors column is updated to add or delete a row to the list..

Also,  if you are constantly inserting rows within existing rows, then the "Unique IDs are all over the place = and therefore require you to use the "FInd" function to lead you to the correct row. ...so something to keep in mind if you decide to go this route. 

 

dled

I was challenged to figure this out, so I used Nel C's instructions above and created a helper sheet that allows for unique columns for each Successor Collector.

• First, add 3 columns to your Schedule sheet [ RowNum =0+1], [ROW_ID=COUNT(RowNumCtr$1:RowNumCtr@row)], [Successors=VLOOKUP([ROW_ID]@row, {SuccessorSheet successors6}, 6, false)]
• Then create your SuccessorSheet that has a unique column for each Task. I started out naming them 1, 2, 3 but then found you can cut/paste from Excel to add 50 columns at a time. After that, I just referenced the default column names (starting at Column 10).
• You can see the formulas in the screenshot. All were taken and modified from Nel's post. The Collector column in blue is the tricky part as each row needs to call out a unique column. That's where the green comes in. Use those columns to create your unique row formulas (blue).
• Smartsheet does limit to 200 columns so you'd have to have an IFERROR to check 2 different sheets to get to a normal project task list. [Successors=IFERROR(Lookup here, Lookup there)]
• Once you create your helper sheet, you can copy it over and over for different projects and just change one column VLOOKUP reference. The rest of it shouldn't change.

Hopefully this is helpful.

Tanner Polzin

I came up with my own solution for indicating if all predecessors are finished. You can see my solution here: https://community.smartsheet.com/discussion/75593/formula-to-indicate-a-task-is-ready-because-predecessors-are-complete-solution/