Extract Cell History For Formulas

bhope51 · 07/19/19

Hello,

Is there any way to extract the Cell history to calculate formulas.

What I am trying to do is create metrics off the dates in the cell history. I.E. if a Dropdown list column starts on July 1 with Value 1 and changes to Value 2 on December 10th then Value 3 on December 21st. I want to measure and tract the time spent between the changes.

Thank you.

Brian

Andrée Starå · 07/19/19

Hi Brian,

Unfortunately, it's not possible to access the cell history data in the sheet at the moment, but it's a great idea!

Please submit an Enhancement Request when you have a moment.

A possible workaround could be to use a third-party service like Zapier or maybe it could work with the Smartsheet API.

Would any of those options work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Emlyn Childs · 07/19/19

Hi Brian

I have devised a workaround when I wanted to calculate how long it took each approver to approve the workflow.

My solution was to create a separate secondary sheet for every approver and use a cross sheet reference from my main sheet to update the secondary separate sheet. The secondary separate sheet only contained the approval column “with cross sheet reference formula” and a system “modified” column that updated automatically as the approval in the main sheet was changed. I then inserted a column in my main sheet that cross sheet referenced the “modified” date from the secondary sheet.

Finally I had the date when the approval was made and could calculate the duration from the previous approval or the created date in the main sheet.

Hope you can make sense of my explanation for your application.

Good luck

Emlyn Childs

Andrée Starå · 07/19/19

Hi Emlyn,

Nice workaround!

Thanks for sharing!

Best,

Andrée

bhope51 · 07/24/19

Hi Emlyn,

I love this work around. It will take a TON of setup on our side. But this solves soooooooooooooo many issues right now. Thank you so much for answering.

Brian Hope

Chelsea Smith · 08/08/19

Is there already an enhancement request for this? If so, I want to vote for it! Please advise how to find it, if it exists. Otherwise, I will create a request... I think many people want to be able to have the cell history referenced in either a formula or another cell within a sheet.

Andrée Starå · 08/09/19

Hi Chelsea,

There isn't any public list available of the enhancement requests already submitted. You can add a new one or vote for something that's already in another post, but you'd have to submit a new enhancement request for both scenarios.

Please submit an Enhancement Request when you have a moment.

A New Way to Submit Your Feature Requests

To make your Enhancement / Feature Request count, send in the form above because there isn’t a guarantee, it will be registered otherwise.

Original Post: https://community.smartsheet.com/announcement/new-way-submit-your-feature-requests

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Klefisch · 04/04/23

Hi,

Has this been implemented yet? Any update?

Thanks!

bhope51 · 04/04/23

@Klefisch ,

Nope, Still would be nice to have and not have to pay for an outside tool that charges each time it runs.

Formulas and Functions

Formulas and Functions

Extract Cell History For Formulas

Comments

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula Help

Unparseable Error

Formula to return the non-blank cell