collect with conditional statement to return all values

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.
https://app.smartsheet.com/b/publish?EQBCT=108bce1f756c44dcae397e87b19a2a7b
is the sheet that i've been trying to get this to work on.
Comments
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!