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

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]), "; ")

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…
Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…