Record Values of cells at intervals

[Pavertheway]

Hi,

I have a spreadsheet that in cell A1, has a percentage value that is constantly changing based on different data being inputted into the sheet.
Is there a way for each time that a change log is registered in that cell, for the cell value to be copied into cell A4. Then the next time there is a change, for the new value to be copied into cell A5, and so on.
So each time A1's value changes, it records the new value, and keeps a log of all the values over time.

Is there a way that I can do this, or will it have to be user inputted?

Thanks

 

[Joe4]

Welcome to the Board!

I would recommend creating some Worksheet_Change code that is triggered off of the range where you are inputting the data. Then, anytime data in that range is manually updated, you can have it automatically copy the value from cell A1 down column A.

If you need help with that, please provide specific details of the range where you are manually inputting data.

 

[Pavertheway]

Hi, thanks for the welcome!

So cells E3, G3, I3, K3, M3, O3, Q3 all hold a percentage, that is shown from ='Sheet2'!AC2 (and then each cell copies from a different sheet), so I would like each time E3 is updated, for D21 to reflect the new value, then at the next change for D22 to change, and so on. Then when G3 has a change, for E21 to update, then E22, and so on.

Does that make sense? Thank you for your reply.

 

[Joe4]

Herein lies the problem:

Worksheet_Change VBA procedures automatically run when cells in a specific range are updated manually. However, they do not capture updates to values returned by formulas.
Worksheet_Calculate VBA procedures automatically run when ANY recalculation happens on the sheet, but it can specify which cell was recalculated.

Therefore, if you want to "capture" the moment a formula field's value changes, you need to look at the underlying cells in the formula, and create a Worksheet_Change VBA procedure to run when one of those are updated manually.

 

[Pavertheway]

Okay, so could I create a new sheet, with the columns on that reflected changes across all the sheets? So when 'Sheet2'!AC2 changes, that triggers the worksheet_Change VBA, which then updates a column in sheet9 (for example) in A1, and then A2? and then have 'Sheet3'!AC2 reflected in Sheet9 B1, B2 from a second Worksheet_Change VBA procedure?

 

[Joe4]

Yes, you would have a Worksheet_Change procedure on each sheet to "capture" when your designated cell is manually updated, and when it is, to update your historical log sheet/column.

 

[Pavertheway]

Oh thank you! So I get how I would start, but where would I go from here;
Private Sub Worksheet_Change(ByVal Target As Range)
If[FONT=&quot] Target.Address = "AC$2" [/FONT][FONT=&quot]Then [/FONT]

 

[Joe4]

So, if on Sheet 9, cell E1 has the formula: =Sheet2!AC2, and AC2 on Sheet2 is being manually updated, here is the event procedure code you would have on Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if cell AC2 has been updated
    If Not Intersect(Target, Range("AC2")) Is Nothing Then
'       Copy value from E1 on Sheet9 to botton of column E
        Sheets("Sheet9").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet9").Range("E1").Value
    End If

End Sub

Note, that it copies the new/current value down column E (so the history is instantaneous, as it is copying the current value, not the previous one).

 

[Pavertheway]

That works, thank you!
But like you said, it works only if AC2 is being manually updated; where it is updated by a formula, Worksheet_Change cannot notice that as a change, and Worksheet_Calculate scans the whole sheet for any formula changes.
So, if AC2 is =Y2/AB2, where Y2 is a user inputted number, and AB2 is a constant, is there anyway to use Worksheet_Change to run through Target.Dependents.Address and still return the change that has occurred in AC2 because Y2 was updated?
Or, can we denote a Worksheet_Change in Y2, then copy AC2?

Thank you again for all your help!

 

[Joe4]

You can use the code I gave you, just have it look for the change in Y2 instead of AC2, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if cell Y2 has been updated
    If Not Intersect(Target, Range("[B][COLOR=#ff0000]Y2[/COLOR][/B]")) Is Nothing Then
'       Copy value from E1 on Sheet9 to botton of column E
        Sheets("Sheet9").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet9").Range("E1").Value
    End If

End Sub