Best Of

Hello Smartsheet. The Collections addition seems like a fine solution and looks to resolve the folder sharing issue that, for 3 documented years, has been asked about. It is disappointing that Smartsheet didn't offer this to customers with a Business Plan. My struggle is in selling my company leaders on double the license cost for a few extra features. I'm working on proving the ROI for the Enterprise plan AND adding Resource Manager AND Dynamic View AND Control Center. I'm new to Smartsheet and am thankful for this community and the resources available. If anyone has advice on proving ROI, I'm all ears.

Hi @Meredith Rhodes

I created a demo solution based on your scenario.

https://app.smartsheet.com/b/publish?EQBCT=c7de18da4d5e4cf395bc2d28ef44202d (The published demo sheet is editable; you can test this solution by adding a new comment)

The basic method is: When a new comment is added in Sheet A → "Latest Comment" updates → Record the Comment Date → Copy the row to Sheet B.

In Sheet B, a formula combines the Comment Date and Latest Comment (Comment Date + ": " + Latest Comment) to easily track when each comment was made.

In Sheet A, we use a JOIN(COLLECT()) function to retrieve the comment history based on the Trial ID, combining all related comments with their dates. I used Sheet Summary Fields and a Show Comment History checkbox to keep the sheet clean, so only the selected row displays the history at a time.

Concept

Each clinical trial (Trial ID) has a unique row in Sheet A (the master sheet). Sheet B records amendments or conversation records related to these trials, where multiple rows can share the same Trial ID.

Automation Workflow

1. When New Comment Added
   • Trigger: When a row is added or changed AND "Latest Comment" changes to any value.
   • Action: Record the current date in the "Comment Date" column.
2. When Comment Date is Today
   • Trigger: When a row is added or changed AND "Comment Date" changes to any value.
   • Action: Copy the row to Sheet B (Comment Record Sheet) and then clear the Comment Date.

Note: We avoid using the system "Created" column because copying rows with system columns can break the automation.Clearing the "Comment Date" after copying ensures multiple comments made on the same day are handled properly.

Displaying Comment History in Sheet A

• Add a checkbox column ("Show Comment History") to decide which Trial ID’s history to show.
• In the "Comment History" field, pull related comments from Sheet B using:

=JOIN(COLLECT({Date & Comment}, {Trial_ID}, IFERROR(INDEX(COLLECT([Trial ID]:[Trial ID], [Show Comment History]:[Show Comment History], true), 1), "")), CHAR(10))

Only the first selected checkbox will be active, making the sheet easy to navigate without cluttering the view.

Also use:

=IF([Show Comment History]@row, [Comment History]#)

to control visibility so that only the selected row’s comment history is displayed.

https://app.smartsheet.com/b/publish?EQBCT=12157ccce978407ba1e638a3b8fd4945

Until Smartsheet introduces an official feature for a central location for user comments across sheets (as Tiffany suggested here), this approach can serve as a workaround.