IF(JOIN(COLLECT())) issues

Dakota Haeffner

Hello,

I have the below formula working correctly and returning the Rally Status if the User Story field has one User Story in it. Unfortunately, I need it to return all the status separated by a delimiter of ", ". For example, 'US1234567, US1234568, US1234569', I need to return status like, 'In Progress, Closed, Grooming.'

=IF([User Stories]@row = "", "No US#", (JOIN(COLLECT({Rally Status}, {User Stories}, @cell = [User Stories]@row), ", ")))

See the below screenshots:

Sheet where formula is being put (Specifically in the Rally Flow State column:

Sheet where data is being pulled from:

James Keuning

Are you only ever going to have four user stories in the User Stories field? I would parse those into four helper columns, evaluate each column, and then combine the Status for each.

Dakota Haeffner

@James Keuning That's Honestly a great idea but we have upwards of 20 sometimes. I could totally use that as a last resort.

Any suggestions on how I could go about formulating those helper columns?