How to Track when a Sheet Summary Field is Changed and the old and new value

Karen Hansard

Hello,

I have a dashboard and on this dashboard I would like to display projects whose sheet summary "Status" field changed from one value to another during the last 30 days. How can I go about doing this?

Thank you!

Thinh Huynh

Hi @ Karen Hansard ;

I got an idea for your use case:

Step 1 : In the source sheet (sheet B), you create a new system-generated Modified date column to record the time of changing of the Project Status and a Copy of Modified date as below

Step 2 : Create another blank sheet, sheet A

Step 3 : In sheet B, create a Copy row automation to copy the row from sheet B to sheet A when there is a change in Status field

You can record the changing in Sheet A as below

Step 4 : You can use the data in sheet A for showing on your dashboard

Hope that helps!

Thinh Huynh

Hi @ Karen Hansard ;

I got an idea for your use case:

Step 1 : In the source sheet (sheet B), you create a new system-generated Modified date column to record the time of changing of the Project Status and a Copy of Modified date as below

Step 2 : Create another blank sheet, sheet A

Step 3 : In sheet B, create a Copy row automation to copy the row from sheet B to sheet A when there is a change in Status field

You can record the changing in Sheet A as below

Step 4 : You can use the data in sheet A for showing on your dashboard

Hope that helps!

Karen Hansard

Hi Thinh - Thanks for the suggested solution. I'd like to track the change in Status which is a Sheet Summary field but I suppose I could use a formula/reference to bring it into the sheet itself. I'll give this a try.

Andrée Starå

Hi @Karen Hansard

To add to Thinh's excellent advice/answer.

Please have a look at my post below with a similar method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Karen Hansard

Hi Thinh - I attempted your scenario and it worked for the use case you have, however my use case is specifically when a Sheet Summary Field is changed, as opposed to a cell in the sheet itself.

Here's my scenario . I have a sheet (A) that is made up of column cells with formulas referencing the Sheet Summary fields for 10 projects in our portfolio. Each row in sheet A represents the Sheet Summary fields for each of the 10 projects. When I directly change a value in my sheet A (essentially overiding the formula), the solution you provided works. However, when I change the original source value of a Sheet Summary field in one of my 10 projects, while my sheet A is updated based on the formula reference the project sheet Summary field, it doesn't trigger the workflow to copy to a new sheet, because the update is made indirectly from the source project sheet. I'm not sure if you were able to follow that, but the difference in my case is that I'm trying to track the change to a Sheet Summary field.

I wasn't able to get the =Modified@row to work on the Copy of Modified Date column. How did you define the Copy of Modified Date column? Was it defined as text or date field? I tried both to no avail.

Thanks so much for your help

Karen.

Thinh Huynh

Hi @Karen Hansard,

Sorry for my late reply. I have not tried your use case that use sheet (A) made up of column cells with formulas referencing the Sheet Summary fields for 10 projects.

Can you do a screenshot of your sheet (A) that show a detail formula referencing the Sheet Summary field, and a screenshot of the Sheet Summary field of a Project?