Accumulated Value Column

Cruz Ortiz

Hi,

Has anyone faced the need to use an accumulated value column?

This has been around for years for those who need to programm an accumulation of historical values in other apps and I have done it using multiple rows to record individual changes then another column to record the accumulation. Nothing super fancy really formula wise.

This becomes challenging in Smartsheet if we do not want to use multiple rows but just ONE attemting to keep record in a separate column of the accumulated value of a cell that is changing randomly its value.

Have you solved this before?

I could not find any formula nor trick to make it work yet.

I will welcome your suggestions or ideas.

Thanks

Genevieve P.

Hey @Cruz Ortiz

If you're on an Enterprise plan, you can use Work Insights from the side panel to see calculations across time on a specific column. Here's more information: Use Work Insights to visualize your data

Cheers,

Genevieve

Cruz Ortiz

Hello Good day,

Thank you Genevieve.

Interesting. Unfortunatelly I do not have Work Insights feature. I will look into it now but it may not be easy to try on unless we purchase it.

If anyone else had an alternative suggestion suitable to a Bussiness plan license, please share.

Thanks again.

Cruz

Cruz Ortiz

Ok, now I have Work Insights and unfortunatelly it does not do the job.

Please

Acum Value Column.png

see attached, you will see the "Days under Reporter" column will be changing when the ticket goes to the reporter (randomly ticked) and counts the days since BUT the Acum column is wanted/expected to accumulate all those days in one single row but so far I cannot achieve.

Any thoughts how to solve?

Thanks

Genevieve P.

Hi @Cruz Ortiz

Do you want your Accumulated Column to Sum or add together all the values in the "Days Under Reporter" column for a Total?

If so, you can use the SUM function to do this:

=SUM([Days Under Reporter]:[Days Under Reporter])

Cruz Ortiz

Hi Genevieve

Not that simple.

I want the Acum ROW cell to add the total days count of "Days under reporter" when the ticket goes back to the reporter (randomly). When ticket goes back to the reporter the "Days under Reporter" count restart a new return days count.

I hope that makes sense.

Cheers,

Genevieve P.

Hey @Cruz Ortiz

Thank you for explaining further, I understand now. There currently isn't a way for a formula to read a cell's historical data, it can only look at the display value being surfaced in each cell of that row. This means that if your Days Under Reporter cell changes back to 0, the Accumulated Column will see 0, not the previous data.

The Work Insights panel does access cell history to display information, but it does not show this on a row-by-row basis in the grid of the sheet itself and requires specific columns to look for data in, as you noted.

Depending on when the original clock starts for the counter, you could use a Record a Date workflow to record the first date that the initial clock started, then use your formula to look at that static date instead, if that helps.

Please also add your vote and voice to these Product enhancement ideas:

• Enhancement Request: access values stored in cell history
• Workflow - list historical values on cell(s) based on criteria

Cheers,

Genevieve

Cruz Ortiz

Hi Genevieve

I thank you for your feedback.

Definitely something Developers should consider.

I will solve it for now with a hep sheet to copy over the row every time the Go back to reporter is triggered, then add up all the same ID rows. I guess there is no other way for now.

Cheers,