Help using join & collect function with multiple criteria & having the delimiter be a semi-colon and

LGustin

I've been able to successfully use join and collect function with 1 criteria, but not two and am also curious if I can have the results within a cell space to look like Ctrl+Enter was used after each one to be able to easily read through all of the results. 

Working formula with 1 criteria:

 =JOIN(COLLECT({Product Roadmap full name Range 1}, {Product Roadmap Release Month Range 1}, Release@row), "; ")

 

Non-working formula trying to use 2:

=JOIN(COLLECT({Product Roadmap full name Range 1}, {Product Roadmap Release Month Range 1}, Release@row, [{Product Roadmap Lane Range 1},Lane@row]), "; ")

Paul Newcome

It is two different ranges. Release Month and Lane.

 

Are you referencing two different sheets in your ranges, or are all of the ranges on the same sheet?

 

Also... You have an extra square bracket tucked away in your second formula...

=JOIN(COLLECT({Product Roadmap full name Range 1}, {Product Roadmap Release Month Range 1}, Release@row, [{Product Roadmap Lane Range 1},Lane@row]), "; ")

changes to

=JOIN(COLLECT({Product Roadmap full name Range 1}, {Product Roadmap Release Month Range 1}, Release@row, {Product Roadmap Lane Range 1},Lane@row]), "; ")

Melodie Nixon

I just came across an answer to this exact question on another thread while looking for answers to a different question I have. A bit late to the party, but maybe it will help?

It certainly helped me!

https://community.smartsheet.com/discussion/feature-request-join-carriage-return-separator

Andrée Starå

Hi Melodie,

Thanks for sharing!

It's even simpler now with the new formulas.

New:- give this one a try 

Use CHAR(10) to enter a line break in your formula when joining text (make sure word wrap is enabled). 



=JOIN([Address1]1:[City, State, Zip]1, CHAR(10))

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting