Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Join(Collect question

Hello,

I am trying to join all notes in a cell from another sheet if the phone number on that row matches the phone number on my current sheet's row. I can do this in google sheets but struggling to translate to smartsheets.

=JOIN(COLLECT({Notes}, {Phone #}, [Phone #]@row), ", ")

This is returning 9 commas for each cell. I've double checked references and formats.

Thanks in advance!

Tags:

Answers

  • Community Champion

    Are there blank "Notes" associated with the phone number in your lookup range? Update the formula to exclude blanks and see if it works:

    =JOIN(COLLECT({Notes}, {Notes}, NOT(ISBLANK(@cell)), {Phone #}, [Phone #]@row), ", ")

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Thank you! That did take care of the comma issue, but it is still not returning the actual notes. The whole column is blank.

  • Community Champion

    Are you able to share a screenshot of what your source sheet looks like? I'm wondering if it's just a small formatting issue or something.

    Another thing you can verify is that the cross sheet ranges are correct. Sometimes if you select an entire column, the pop-up window will refresh without you realizing it and re-selects just the first row cell - I've had many frustrating times with formulas because of this happening.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • I can't post client's phone numbers but I can say the vlookup formula works just fine using the same two columns as the reference. Double checked the references and the whole column is selected.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions