Extract Cell History For Formulas

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

Find more posts tagged with

Construction

Formulas

Government

Support

Product Development

Project Management

Enhancement Request

Comments

Andrée Starå

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

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å

Hi Emlyn,

Nice workaround!

Thanks for sharing!

Best,

Andrée

bhope51

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

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å

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

Hi,

Has this been implemented yet? Any update?

Thanks!

bhope51

@Klefisch ,

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…