collect with conditional statement to return all values

L_123 ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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
    L_123 ✭✭✭✭✭✭

    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
    L_123 ✭✭✭✭✭✭

    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)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!