Moving Comments Between Sheets

Katie M

I am looking to find a method to copy select columns in a row along with the row comments from one sheet to another.

Methods that I have tried and will not work in this scenario:

Copying the entire row from Sheet A to Sheet B (This brings over all columns and I am only looking to copy over select columns)

Using helper sheets with Index Match (I cannot bring over the comments - unless I am missing something)

Longer description of the scenario: Sheet A is an Opportunity tracking sheet with several columns used to track an opportunity from prospect to closure and comments are added to keep track of any nuances in the sales process. Sheet B is a master Sales Sheet that lists all won opportunities with additional columns to be populated along the engagement process.

Goal: Move select columns and all comments associated with the row from Sheet A to Sheet B upon closure of a sale and retain comments for historical data on client conversations that will be useful throughout the engagement period (which is tracked on Sheet B)

jmyzk_cloudsmart_jp

Hi @Katie M

You can use the "Latest Comment Column" feature.

Step-by-Step Solution

1. Create Sheet C:
   • Create a new sheet named "Sheet C." This sheet will store rows copied from "Sheet A" whenever comments change.

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

1. Add a Latest Comment Column in Sheet A:
   • Use the "Latest Comment" column feature in Smartsheet to automatically display the most recent comment for each row. This column lets you see the latest feedback without opening the conversations panel. Learn more about the Latest Comment column feature.

https://app.smartsheet.com/b/publish?EQBCT=db8322b904f94bedb1eb096e14cb64dd (Link to the published demo sheet)

1. Set Up Automation in Sheet A:
   • Go to the Automation menu in "Sheet A" and create a new automation rule.
   • Trigger: Set the trigger to "When a row is changed."
   • Condition: Add a condition to specify that the change should be in the "Latest Comment" column.
   • Action: Set the action to "Copy row to another sheet" and choose "Sheet C" as the destination.
   • This automation ensures that whenever a comment is added or updated in "Sheet A," the entire row is copied to "Sheet C."

1. Link Sheet B to Comments from Sheet C:
   • In "Sheet B," add a column named "Comments" or another suitable name where you want to display the comments.
   • =JOIN(COLLECT({Latest Comment range of Sheet C}, {ID column range of Sheet C}, [ID of Sheet B]), CHAR(10))
   • This formula does the following:
     • COLLECT retrieves the "Latest Comment" from "Sheet C" where the IDs match between "Sheet B" and "Sheet C."
     • JOIN combines all comments related to a specific ID, separating them with a line break (CHAR(10)).

https://app.smartsheet.com/b/publish?EQBCT=2e0abd1f4d254df1b29ae60b9fa2ab34

Comments Formula in Sheet B

=JOIN(COLLECT({Sheet C Range : Latest Comment}, {Sheet C Range : Primary Column}, [Primary Column]@row), CHAR(10))

Additional Notes

• ID Column: Ensure there is a unique ID column in all sheets (Sheets A, B, and C) that can be used to match rows correctly. (In the demo solution, we used the primary column's values for the demo purpose, assuming they are unique.
• Historical Data: By copying the rows to "Sheet C," you maintain a history of all changes, and linking to "Sheet B" ensures that relevant comments remain accessible for the engagement process.

This method efficiently uses Smartsheet's features to keep all necessary data consistent and readily available across multiple sheets.