Excluding cells from COLLECT based on value

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

Hi All,

I am trying to write a COLLECT which excludes results based on the value of a cell, similar to how NOT(ISBLANK(@cell)) works.

Below is a formula I have made which works, but I was hoping there was a more succinct way to achieve the same result 

=JOIN(IF([Audio Language]153 <> "English", COLLECT([Audio Language]153:Courier153, [Audio Language]153:Courier153, NOT(ISBLANK(@cell))), COLLECT([Subtitle Language]153:Courier153, [Subtitle Language]153:Courier153, NOT(ISBLANK(@cell)))), " - ")


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That looks like it's about it. Based on how I'm reading it, I wouldn't be able to provide a more efficient way of writing it. I would call it a job well done. yes


    The only bits you could change would be to specify ISNUMBER, ISTEXT, or ISDATE, but the only way that would work is if every field you want to join is going to be the same format (number, text, or date), but the way you have it written it will collect every format so long as it's not blank.


    It's the most comprehensive way of writing it without having a bunch of extra mess written in.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!