collect with conditional statement to return all values

L_123 · 07/26/18

I am attempting to use a collect with no criteria, that is it grabs everything and am having some problems.

=JOIN(COLLECT(Return:Return, a:a, IF(ISBLANK(a10), true)))

my desired behavior is that if cell A10 is blank, all of the values of Return (including where A is blank) are collected.

This is part of a much larger equation that will allow for user sorting for some basic data analysis and chart building.

is the sheet that i've been trying to get this to work on.

L_123 · 07/26/18

welp. solved my own issue in a way I wasn't expecting. I can conditionally use a range inside of a collect statement.

=JOIN(COLLECT(Return:Return, IF(ISBLANK(a10), a:a), 1))

this will probably make my formula a lot more complicated, but its nice to know I can do that.

L_123 · 07/26/18

found the original solution I was looking for as well. kinda redundant but it works

=JOIN(COLLECT(Return:Return, a:a, IF(ISBLANK(a10), @cell = @cell)))

Comments