Can I concatenate a field call with a predecessor field #

E. Robert Shanfeld

I am looking to call a field based on the content in the predecessor column.

In this case, I can manually type in "='is blocked by' + [Issue Key]9". Once Issue Key is populated (let's say Issue Key = HelloWorld01), this will read as "is blocked by HelloWorld01". The following row may read "HelloWorld95".

Rather than typing "[Issue Key]9" I'd like for the 9 to be populated by the "predecessor" column. Essentially calling "[Issue Key](predecessor value)". Any ideas?

Note - there may be multiple predecessors, at which point it will need to adjust.

Note 2 - this is used for connection to JIRA to acknowledge blocked tasks

Genevieve P.

Hi @E. Robert Shanfeld

If you're using this Predecessor column in your project settings, then the value is housed differently than a regular number and we'll need to use a helper column to translate the 9 to a value. Then you could use an INDEX function to return the Issue Key text for the row identified in the Helper Column.

I have this formula broken down in another Community post, here.

https://community.smartsheet.com/discussion/comment/298474#Comment_298474

Keep in mind that if you had any lag or lead time the formula would break (ex. "FS 9 + 2d") since now that's text instead of a single number.

Let me know if this would work for you!

Cheers,

Genevieve

E. Robert Shanfeld

Hi Genevieve,

This works if there is one predecessor, but not if there are multiple predecessors. Same issue on my end that I cannot solve, but you've helped me move forward in some respect. My end goal is to carry predecessors in SmartSheet over to "blocked tasks" in Jira.

Genevieve P.

Hi @E. Robert Shanfeld

For the rows where there are two listed, we can use the CONTAINS function to see if the helper column contains a comma. If it does, then we'll add two INDEX formulas together... one that looks at the LEFT of the cell and one that looks at the RIGHT of the cell. Does that make sense?

Try this:

=IFERROR(IF(CONTAINS(",", P@row), INDEX([Issue Key]:[Issue Key], VALUE(LEFT(P@row, 1))) + " and " + INDEX([Issue Key]:[Issue Key], VALUE(RIGHT(P@row, 1))), INDEX([Issue Key]:[Issue Key], VALUE(P@row))), "")

Cheers,

Genevieve

E. Robert Shanfeld

Hi Genevieve-- Thank you for the guidance! This is close, but not exactly what I need. I would like for it to read as "is blocked by [Issue Key], is blocked by [Issue Key]" for more than one issue key. I have adjusted your equation to read as

=IFERROR(IF(CONTAINS(",", P@row), "is blocked by " + INDEX([Issue Key]:[Issue Key], VALUE(LEFT(P@row, 1))) + ", is blocked by " + INDEX([Issue Key]:[Issue Key], VALUE(RIGHT(P@row, 1))), INDEX([Issue Key]:[Issue Key], VALUE(P@row))), "")

Unfortunately, I notice 2 issues--

1) if my predecessor is two digits (e.g. the predecessor is "row 34"), then it will pull from the first digit in the predecessor ("row 3" in this case, rather than "row 34")

2) this will limit me to just two predecessors. This is good, but curious if you have ideas for multiple (for future stability). How could I make this scalable for (reasonably) limitless predecessors--perhaps up to 5?

Genevieve P.

Hi @E. Robert Shanfeld

Good call-outs, I hadn't thought of those scenarios as I personally don't have multiple predecessors.

Here's the update for 1)

Instead of just seeing if the cell CONTAINS the comma, we can use the FIND function to identify where the comma falls in the cell. Then based on its position, we will grab either 1 or 2 values for the INDEX Function.

=IFERROR(IF(FIND(",", P@row) = 2, "is blocked by " + INDEX([Issue Key]:[Issue Key], VALUE(LEFT(P@row, 1))) + ", is blocked by " + INDEX([Issue Key]:[Issue Key], IFERROR(VALUE(RIGHT(P@row, 2)), VALUE(RIGHT(P@row, 1)))), IF(FIND(",", P@row) = 3, "is blocked by " + INDEX([Issue Key]:[Issue Key], VALUE(LEFT(P@row, 2))) + ", is blocked by " + INDEX([Issue Key]:[Issue Key], IFERROR(VALUE(RIGHT(P@row, 2)), VALUE(RIGHT(P@row, 1)))), "is blocked by " + INDEX([Issue Key]:[Issue Key], VALUE(P@row)))), "")

For 2) my formula is based on the assumption there will only ever be one comma, using this as a unique starting point to grab left or right data. I have to admit I'm out of ideas for 3+ Predecessors.

To start, does the above formula meet all the current criteria, now?