Excluding cells from COLLECT based on value

Matt_O
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)))), " - ")

Tags:
· Share on FacebookShare on Twitter

Comments

  • 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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!