I'm using a JOIN to collect values from another sheet. The range includes two columns on the other sheet. I am collecting the first column, so long as the first column contains a string and the second column is not blank.

I have this but it isn't working:

JOIN(COLLECT({PB-CMR-Master}, {PB-CMR-URL}, CONTAINS(<>"", @cell), {PB-CMR-Master}, CONTAINS([CUR-MOD]@row, @cell)), " / ")

{PB-CMR-Master} is the first column, which I am collecting, if it contains a string [CUR_MOD}@row

{PB-CMR-URL} is the second column in the other sheet, which should NOT be blank, to collect the first column

I am not sure if I set up the criterion that evaluates the second column to NOT be blank.


  • Think I figured it out:

    JOIN(COLLECT({PB-CMR-Master}, {PB-CMR-URL}, NOT(ISBLANK(@cell)), {PB-CMR-Master}, CONTAINS([CUR-MOD]@row, @cell)), " / ") 

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Art Schneiderheinze

    I'm glad you figured it out! Thanks for posting your solution.

  • PeggyLang
    PeggyLang ✭✭✭✭✭

    @Genevieve P.

    I have a form with numerous logic criteria.

    If select Door then there are further options.

    If select Fire-Life-Safety then there are further options.

    If select HVAC then there are further options . . . and so on.

    In my worksheet I want 'Request Code' to be a collection of anything from 'Request' Codes thru to 'Roof'

    In example above I really only want 'Request Code' to illustrate 'HVAC-Odor', but I am getting all of the delimiters of the empty cells between 'Request Codes' and 'HVAC'.

    Is there a way to 'skip' the empty cells? (so I don't get a collection of '-''s?)

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @peggy lang

    Yes! We can use the same format as the formula above, with JOIN(COLLECT, specifying the cells in your range need to be "not blank", like so:

    =JOIN(COLLECT([Request Codes]@row:[Roof]@row, [Request Codes]@row:[Roof]@row, <>""), "-")



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!