How to keep a history of changing values in a cell?

[zb134]

I hope I can explain this clearly in words as it is in my mind!

Lets say I have C2, D2 and E2 as my cells for vendor costs, IT costs, Employee costs (or whatever, the actual table has a lot more components)
B2 is just summing these up. So B2= sum(C2:E2) in this case.

The thing is as I get different quotes I will re-visit C2,D2 and E2 to change the numbers in those cells(and a result the summed value in B2 will obviously change).

I am hoping that there is a macro solution that will allow me to store the values of B2 as they change in cell A2 so I have a history of these.
So lets say my B2 value is 100 today but I get a new quote tomorrow that makes B2= 90 then I want A2 to show 100,90 (the order or type of separator doesn't matter, but basically a history of what was in my B2)

Thanks in advance for any help or suggestions you can offer.

 

[RedBeard]

Track changes maybe?

 

[zb134]

Track changes maybe?

But that will track all changes happening in a large and complex workbook with thousands of variables


What I am looking for is more a neat history of values calculated in a certain cell

 

[tygrrboi]

You would need to use VBA. Is that something you would consider?

 

[zb134]

Yes, that's exactly what I was hoping to use

 

[tygrrboi]

This should work for you. Try it on a copy of your data so you don't ruin your actual data.

Put this code in the VBA window for the sheet you are working in. (look at your Project window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

If Target.Column <=5 And Target.Column >=3 Then 'Checks if the change was between columns C and E (3 and 5)
    With Range("A" & Target.Row)
        If .Value <> vbnullstring Then
            .Value = .Value & ","
        End If
        .Value = .Value & Range("B" & Target.Row).Value
    End With
End If


End Sub

Also, a possible issue... This will update itself whenever there is a change to EITHER C, D or E... so lets say you want to change it from 2, 3, 4 (SUM = 9) to 3, 4, 5....
first you change C from 2 to 3 and the sum will be 10... then you change D from 3 to 4 and the sum becomes 11, and finally change E from 4 to 5 and the sum becomes 12...
A will read "9,10,11,12" where as possibly you might just want it to read "9,12"

Is that a possibility to consider? or are you happy with "9,10,11,12" for example?

 

[zb134]

First of all, thank you for taking the time to help me. I appreciate it!

Yes, that is an issue I didn't foresee but this is definitely a lot better than nothing.

Here's the thing, I was simplifying when I said c2,e2,d2. The actual cells to be summed are in a different worksheet from the one where they get totalled (b2). I'm wondering if there is way to make it so that the appending to A2 happens only when active worksheet is changed. So after I make all the changes and return to the worksheet only then he gets added to a2

 

[Mathias Pedersen Heinze]

Another approach could be to store the values in a named range. You would have to use VBA for this as well. This code will generate a new named range called SumHistory and append the current value in B2 to that list whenever the sub in run.

Sub StoreHistory()

Dim nName As Name
Dim dSumValue As Double
Dim sTempString As String
    
    On Error Resume Next
    Set nName = Names("SumHistory")
    dSumValue = Range("B2")
        
        If nName Is Nothing Then
                ActiveWorkbook.Names.Add Name:="SumHistory", RefersTo:=dSumValue
            Else
                sTempString = Evaluate(Names("SumHistory").RefersTo)
                sTempString = sTempString & "," & dSumValue
                ActiveWorkbook.Names.Add Name:="SumHistory", RefersTo:=sTempString
        End If


End Sub

 

[tygrrboi]

Will there be more than one row of these values?

Like sums in B2, B3, B4 .... B1000 ?



Here is what I think you want.. Please correct me if I have the process wrong.

You have Sheet 1 which has your content. Column B holds the sums of the Columns C-XXX in their respective rows.

You will be making changes anywhere in C and XXX for a specific row. Sometimes one cell in a row, sometimes more than one... Sometimes you will make changes to several rows at a time.

Those changes (however many you make) are one instance.

Then you will either click a button, or move to Sheet2 and the history of that one instance will be updated, for every row in column A (matching the values in Sheet1 Column B)

If there was no change to a certain row, there will be no addition to the history sheet...



Is this correct?

 

[tygrrboi]

Ok... because I might be busy later I thought I'd give you the solution I described above... except I realised you have the SUMs in the same sheet as you want the history. Also, the individual values in a separate sheet...

For this code, the sheet with the individual values do not matter in the code because the calculation is assumedly done in the cell with the SUM function.

I would put this in the code for the sheet which contains the SUMs in column B and will have the history in Column A

Private Sub Worksheet_Activate()
    Dim LastRow As Long, i As Long
    Dim HistoryValue As String


    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    
        For i = 1 To LastRow
            HistoryValue = .Range("A" & i).Value & ""
            If HistoryValue = vbNullString Then[COLOR=#008000] 'if there was nothing in the A cell (history) just put the new value.[/COLOR]
                HistoryValue = .Range("B" & i).Value
            ElseIf --Right(HistoryValue, Len(HistoryValue) - InStrRev(HistoryValue, ",")) <> .Range("B" & i).Value Then [COLOR=#008000]'If the right most value of the history is different than the new sum...[/COLOR]
                HistoryValue = HistoryValue & "," & .Range("B" & i).Value
            Else[COLOR=#008000] 'the right most value of the history has same value as the new value, no change.[/COLOR]
[COLOR=#008000]                'do nothing[/COLOR]
            End If
            .Range("A" & i).Value = HistoryValue & ""
        Next i
    End With
End Sub

This will calculate every time you click on the sum and history sheet... While you have the original sheet (with individual data) activated and you are changing some of the values in there, nothing will happen until you click the Sum & history sheet. Then this will run.

If this is different than the results you wanted please let me know and I can try and change it to suit your needs.