Join(Collect question

trcavine · 09/11/24

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!

Jason Tarpinian · 09/11/24

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

trcavine · 09/11/24

https://community.smartsheet.com/discussion/comment/453572#Comment_453572

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

Jason Tarpinian · 09/11/24

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.

trcavine · 09/11/24

https://community.smartsheet.com/discussion/comment/453590#Comment_453590

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.

Join(Collect question

Answers

Help Article Resources

Categories