Formula that returns the title of any Issue Links in a Smartsheet linked to Jira project

Jamie Koch

Hello! I have a sheet that is linked to a Jira project, and for Bug tickets, I'd like to see the titles of the blocked issues. Typically, I would use an INDEX/MATCH formula, but I'm not sure of the best way to isolate the issue key(s) from the Issue Links field so I can use it as the lookup value. In some cases, a Bug might be blocking multiple issues, so the logic needs to work for all issues.

Here's a screenshot of the Issue Links column and the new column where I'd like to pull the titles in:

I want the formula to look at the Issue Links, then search the rest of the rows in the sheet for the Issue Key(s), and return the Title(s). For example, if the Bug blocks PT03-84, the formula would return the Title value in the row for PT03-84.

I think I might need multiple helper columns, but I'm not even sure if it's possible to do what I need with this many variables. Any ideas would be much appreciated!

Paul Newcome

You might be able to get this to work with a single helper column (haven't tested it with hyperlinks yet - but do know it will work with plain text).

The helper column (called "Blocked Keys" in this example) is a multi-select dropdown in the target sheet with this column formula:

=SUBSTITUTE(SUBSTITUTE([Issue Links]@row, "blocked ", ""), ", ", CHAR(10))

The output should be just the keys "PT##-##" as individually "selected" options in the cell.

From there you would use a JOIN/COLLECT to pull the Titles in along the lines of…

=JOIN(COLLECT({Reference Sheet Titles Column}, {Reference Sheet Issue Key Column}, HAS([Blocked Keys]@row, @cell)), CHAR(10))

The above (once text wrapping is applied to the column) will have each of the titles listed within the cell and put a line break in between if there are multiple titles. If you would prefer a different delimiter, just change that CHAR(10) in the last formula to whatever you need.

Paul Newcome

You might be able to get this to work with a single helper column (haven't tested it with hyperlinks yet - but do know it will work with plain text).

The helper column (called "Blocked Keys" in this example) is a multi-select dropdown in the target sheet with this column formula:

=SUBSTITUTE(SUBSTITUTE([Issue Links]@row, "blocked ", ""), ", ", CHAR(10))

The output should be just the keys "PT##-##" as individually "selected" options in the cell.

From there you would use a JOIN/COLLECT to pull the Titles in along the lines of…

=JOIN(COLLECT({Reference Sheet Titles Column}, {Reference Sheet Issue Key Column}, HAS([Blocked Keys]@row, @cell)), CHAR(10))

The above (once text wrapping is applied to the column) will have each of the titles listed within the cell and put a line break in between if there are multiple titles. If you would prefer a different delimiter, just change that CHAR(10) in the last formula to whatever you need.

Jamie Koch

@Paul Newcome , this worked with some slight tweaks!

Here's the formula I ended up using in the Blocked Keys column:

=SUBSTITUTE(SUBSTITUTE([Issue Links]@row, "blocks ", ""), ", ", CHAR(10))

And in the Linked Issue Titles column:

=JOIN(COLLECT(Title:Title, [Issue Key]:[Issue Key], HAS([Blocked Keys]@row, @cell)), CHAR(10))

Screenshot of output:

Thank you so much for your help! I haven't used the SUBSTITUTE function much, but I'll have to keep this in mind in the future. 😊

Paul Newcome

Happy to help. 👍️

I didn't realize it was all on the same sheet, and good catch on my typo of "blocked" vs "blocks".