JOIN/COLLECT multiple checkboxes into a text/number column

JCardinal

Hi all,

Using Paul Newcome' solution in this 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 found this community post and 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.