Access values stored in cell history
Options
Matheus Vieira
✭✭✭
Hi guys,
my idea here is to use the stored information in a cell value.
Let's say that the first time this cell was changed is important to me and I want to store this value in a different column in my sheet.
I would then use a formula to get this stored information, for example:
=GETCELLHISTORY([info], [position])
info: what information I want i.e. name of who made the change (Matheus Vieira), date of that change (11/11/22), time of the change (2:01 AM - this might be hard because of time zones) or value of the input (in this case another date: 11/9/22, but it could be words, numbers anything stored in that cell, I know that for the case of LAST update, it would be already the value that is shown in the cell, but could be useful to use for the first stored value there). We could also make it as numbers as 1 to name, 2 to date, 3 to time and so on.
position: this would indicate the position of the changes i.e. 0 to first (changes made on 10/24/22), 1 to last (changes made on 11/11/22)
In my example that I want to access the information of the first change of the cell, the formula would be like:
=GETCELLHISTORY(1, 0)
and on my new cell I would return: John Smith.
we could get crazier with the ideias.
In my opinion this would be a great addition to Smartsheet and show the power of this amazing tool
If you find this useful, please upvote so it could be implemented
Thanks! 😊
Tags:
Comments
-
Good day,
I would like to have a new option, in which it is possible to reference the previous value of a cell. At the moment we can see cell history, but we cannot reference a past value.
For example, if this were possible I could make a table showing past dates inside of the same cell.
Can this be added please?
Thanks
Hans De Moitié
-
I'd like to be able to access the cell history with a formula. For instnace, recording the date a checkbox was marked in a date column. I could write a formula for that going forward, but to be able to pull historical data I need to go in and check each cell history and transcribe the date.
-
Formula would be =GETCELLHISTORY([cell], [info], [position])
-
One of the issues we have at the end of month with collecting "time worked or time spent" on a project is how do we see the difference in the hours were entered week to week, or month to month or year to year.
We currently have a field labeled "Actual Hours" and this field is updated manually to show increments of hours being worked on.
We found that the data and information we need to report on is available in the "Cell History", which shows a timestamp on when the updates were made. Is there a way to extract this data easily rather than an "Activity Log" which shows all changes and it is difficult to pin point the changes to the field that is capturing the data we want.
See sample snapshot below.
-
Please make a formula, or enhance the VLOOKUP function, to look up prior cell values. Often, we need to compare values over time, to highlight issues. For example, I have a Top 10 grid that tracks how we're making progress week to week and month to month on the top ranked items in our product plan. To do so, I need to lookup the prior values in a cell using previous dates. The API is a heavy lift for many of us*.
Another use case is here: https://community.smartsheet.com/discussion/75191/last-weeks-value
*Heavy lift of API explained, and a more general idea, is here: https://community.smartsheet.com/discussion/100785/call-apis-from-a-formula/
Thanks for listening, Peter Spung | pspung@northcarolina.edu | @paspung
-
Hi Smartsheet, we use certain columns to track % complete on line items in a product roadmap. Over time, they increase at differing rates, until 100% complete. We want to create a sheet that can show the % complete values over time for a line item. For example, one column would be % Complete One Month Ago. Another would be % Complete Two Months Ago. Another Three Months Ago. And so on.
To do this, the VLOOKUP function would need to take a date (in the past) as an optional parameter. It would retrieve the cell's value on that date, using the cell history.
Of course I can do this now by hand, by examining the cell history for the % Complete column for an item, and inferring what the value was one, two, three... months ago. But I want it automated, using Smartsheet formulas.
Thanks for listening! Peter Spung
-
Here's an example of how an enhancement to the VLOOKUP function, adding a date argument, would be used. a report on the % Complete and Ranking of a product roadmap item over time. The current value, at the current date (and time), of each are looked up and placed in cols 4 and 5 in the grid below using VLOOKUP. Cols 6 thru 11 are looked up manually, using the cell history. Today, the highlighted columns, 6 thru 11, are manually looked up using the Cell History on the same cells as cols 4 and 5. With an enhancement to VLOOKUP, cols 6 thru 11 could also be looked up using the VLOOKUP, supplying the desired date when calling the function: one month ago, two months ago, three months ago.
Thanks for your consideration. Peter
-
I would love this feature! A current scenario where this would be useful is on a Q&A sheet where there could be some back and forth and multiple people contributing to the answer. I would add automation using the cell history, or the backend data during the udpate. Whenever the Answer column is udpated, whoever is updating the cell, their name would be placed into the "Contributors" column. This way if the submitter needs to go back for clarification, or someone in the future needs addt'l info, they easily know who to reach out to.
-
There is an API call to list a cell's history. It would be simple to create a "history" sheet that shows the history as either columns or rows for some or all the cells/rows of a sheet.
/marc
-
/marc, How so? How could that be made available in a formula, so that mere mortals who don't code could use it?
Thanks, Peter
-
The data might look something like:
Row: 1 Column: 6 Name: Program Value: Drug & Vaccine Prototyping Date: 2022-12-13T14:12:00Z Who: Marc Fleischmann
Row: 1 Column: 6 Name: Program Value: #INVALID REF Date: 2022-12-13T13:08:48Z Who: Marc Fleischmann
Row: 1 Column: 6 Name: Program Value: Drug & Vaccine Prototyping Date: 2022-12-12T14:12:24Z Who: Marc Fleischmann
Row: 1 Column: 6 Name: Program Value: #INVALID REF Date: 2022-12-12T13:09:25Z Who: Marc Fleischmann
Row: 1 Column: 6 Name: Program Value: Drug & Vaccine Prototyping Date: 2022-12-08T16:07:50Z Who: Marc Fleischmann
I put the "Row:", etc. just for clarity here, but the data would be in columns.
You have the row, column number, column title, the cell value, the date/time it change and by whom.
You'd collect it by cell/row picking what date you are interested in.
/marc
-
Hi Smartsheet, any news on this? I just completed another monthly report where I needed to look up prior values in multiple cells manually by past dates and number of days. Instead of something like =VLOOKUP(search_value, lookup_table, column_num, [match_type], [value_on_date]) with that hypothetical, long-sought-after, shiny, new, optional parameter value_on_date.
Please let us know how this is progressing on the product roadmap. Thanks! Peter Spung | Raleigh, NC
-
I'm looking forward to it!
-
I can't love this idea enough. This type of data is available in other project management systems and is a gap functionality for project managers.
-
If we can have an automation where we get the cell history on the sheet, we can monitor what changes have been made and what comments have been made and by whom. This is a must-have feature to track.
