JOIN/COLLECT multiple checkboxes into a text/number column

JCardinal

Hi all,

Using Paul Newcome' solution inthis post, I came up with the following working formula: =JOIN(COLLECT($[Add to ABE waitlist]$1:$[Add to Transitions waitlist]$1, [Add to ABE waitlist]@row:[Add to Transitions waitlist]@row, @cell = 1), " AND ")

But I can't convert it to a column formula because of the absolute references. So I foundthis community postand created the Waitlist helper sheet summary field with the following formula: =JOIN([Add to ABE waitlist]1:[Add to Transitions waitlist]1, " AND ")

When I update the Waitlist formula to =JOIN(COLLECT([Waitlist helper]#, [Add to ABE waitlist]@row:[Add to Transitions waitlist]@row, @cell = 1), " AND ") I get an incorrect argument set error.

Any insight would be GREATLY appreciated.

Thanks,

Leibel S

@JCardinal

2 ways this can be done:

Option #1:

Use the INDEX function to feed the first row into your formula:

=JOIN(COLLECT(INDEX([Add to ABE waitlist]:[Add to Transitions waitlist], 1), [Add to ABE waitlist]@row:[Add to Transitions waitlist]@row, @cell = 1), " AND ")

Option #2:

You can have this in a helper sheet and bring it in as a cross sheet reference...

This could make issues if someone moves columns around, but you can also have this helper sheet actually be pulling the data from this main sheet (so that it is always in sync)

Let me know if i am making sense...

Leibel S

@JCardinal

2 ways this can be done:

Option #1:

Use the INDEX function to feed the first row into your formula:

=JOIN(COLLECT(INDEX([Add to ABE waitlist]:[Add to Transitions waitlist], 1), [Add to ABE waitlist]@row:[Add to Transitions waitlist]@row, @cell = 1), " AND ")

Option #2:

You can have this in a helper sheet and bring it in as a cross sheet reference...

This could make issues if someone moves columns around, but you can also have this helper sheet actually be pulling the data from this main sheet (so that it is always in sync)

Let me know if i am making sense...

JCardinal

Index, of course. Thank you very much@Leibel S.