collecting all comments in one cell?

LMR

Hi there,

I am trying to collect all comments in to one cell following these formulas:

=JOIN(COLLECT({Comments}, {Row ID},[RowID]@row))

=JOIN(COLLECT({Comment Only}, {Row ID}, [Row ID]@row), CHAR(10))

Example explaining it:

a way to collect all comments in one cell - Smartsheet.com

however, it keeps giving me #UNPARSEABLE

I also tried following the automation, but it only shows me the last comment, not the whole comment thread.

Could someone please explain how the formula works and what I am doing wrong (also what does CHAR(10) stand for?)

Thank you

Paul Newcome

Are you able to provide some screenshots for context?

LMR

hi, yes sorry for not adding it before

This is the workflow that I used that only shows me the last comment:

this is the formula that I have used but that doesn't work:

=JOIN(COLLECT({Comment Only}, {Row ID}, [Row ID]@row), CHAR(10))

it keeps giving me:

Paul Newcome

The sheet that has the formula in it, does it have a column actually called "Row ID"?

LMR

hi I have added "project ID" to work as the "row ID", but still does not work. Would you mind braking it down on how the formula works? I know it uses 2 different sheets but i am not sure why I can make it work

These are the cell I am using

or if you have a formula that could work

please and thank you

Brian_Richardson

In your case, UNPARSEABLE is because you're just missing a trailing parenthesis in your formula.

=JOIN(COLLECT({Comment Only}, {Row ID}, [Row ID]@row), CHAR(10)) )

However, once you fix that, if you get no result or INVALID errors, it'll be because you need the other pieces to be in place.

COLLECT works by collecting up all the cells in a range that meet the criteria. So COLLECT ({Comment Only}, {Row ID}, [Row ID]@row) is saying "collect for me all the cells in the "Comment Only" column in the other sheet where the Row ID column in that same second sheet has a value that's equal to the Row ID cell on this sheet, on this row"

JOIN then takes that collection of values and turns them into text, separated by whatever you put at the end. In your case the CHAR(10) is the separator which is a carriage return.

When you're using a cross-sheet reference like {Comment Only}, you insert that reference into your formula by clicking the "Reference Cells in another sheet" link in the formula helper popup box, as you type the formula out. If you didn't do that, it won't work. Click that link and select the column in the other sheet that you're trying to reference.

Your [Row ID]@row needs to refer to a real column in the sheet called Row ID. Looks like you changed it to Project ID which is fine, make sure you update the formula though, and make sure that the values in that column match up to the values in the other column that you're trying to filter for in your collection.

If you're using CHAR(10) on your JOIN, then you also will want to highlight the column that has the formula and click the Wrap button to be able to see all the results.

Paul Newcome

@Brian_Richardson The parenthesis are good to go. This is from their last post:

=JOIN(COLLECT({Comment Only}, {Row ID}, [Row ID]@row), CHAR(10))

@LMR You formula needs to use the column name that you are using in your sheet. If you do not have a column called "Row ID", you should not have "Row ID" in your formula.