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!