•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to trend an updating cell automatically
Our take
Tracking the history of your asset values in Excel can enhance your data management strategy. If you have a cell that updates automatically, such as cell B4 for total value, you can effectively log its changes by utilizing a simple VBA script. This method not only records the value alongside a timestamp each time you open the file but also ensures that daily values are saved without duplicates.
So I have this excel tracking my assets and giving a total value in cell B4, it updates automatically or when you F9.
Now I’d like to track the history of this value in a graph by writing the cell’s value away with a timestamp every time I open up the file after it has refreshed that cell.
Which is the best way to handle this Excel Masters? 🙏🏼
Fixed with this VBA script(which also autosaves daily value on open & removes duplicates):
Sub LogAssetValue() Dim wsSource As Worksheet Dim wsHistory As Worksheet Dim lastRow As Long Dim lastDate As Date Set wsSource = ThisWorkbook.Sheets("All") Set wsHistory = ThisWorkbook.Sheets("History") lastRow = wsHistory.Cells(wsHistory.Rows.Count, 1).End(xlUp).Row ' Get last logged date If lastRow > 1 Then lastDate = wsHistory.Cells(lastRow, 1).Value Else lastDate = 0 End If ' Only log if today not already logged If lastDate <> Date Then wsHistory.Cells(lastRow + 1, 1).Value = Date wsHistory.Cells(lastRow + 1, 2).Value = wsSource.Range("B4").Value End If End Sub Private Sub Workbook_Open() Call LogAssetValue End Sub [link] [comments]
Read on the original site
Open the publisher's page for the full experience