Logg calculated cell values

Hi!

I have a cell, that is showing a calculated value. This value changes twice a week because the data it is based on changes. I'd like a way to log what it used to be - so that I can show a % of it going up or down. I cannot use the watch window because I need to be able to calculate that old number with the new one.

Sheet1
Cell A1 = 100
Cell B1 = N/A

Sheet2
Cell A1 = blank

-> data changes

Sheet1
Cell A1 = 200
Cell B1 = +100% (sheet1!A1/sheet2!A1) (will add some more stuff here to make it look better)

Sheet2
Cell A1 = 100


Any ideas? Thanks in advance!

Originally Posted by Bernie Deitrick

Use Sheet1's calculate event, though we need two cells on Sheet2 - the current value will be in A2, and the historical value will be in cell A1. Copy this code, right-click Sheet1's tab, select "View Code" and paste the code into the window that appears. Then save the workbook as a macro-enabled .xlsm

Please Login or Register  to view this content.

You can extend this technique to store more of your data's history to, say, create a chart.

This worked perfectly Bernie! I do have a bunch of cells, do I stack the codes after each other (after I figure out which cells to change)?

Private Sub Worksheet_Calculate()
If Worksheets("Sheet2").Range("A2").Value <> Range("A1").Value Then
Worksheets("Sheet2").Range("A1").Value = Worksheets("Sheet2").Range("A2").Value
Worksheets("Sheet2").Range("A2").Value = Range("A1").Value
End If
End Sub

Private Sub Worksheet_Calculate()
If Worksheets("Sheet2").Range("X2").Value <> Range("X1").Value Then
Worksheets("Sheet2").Range("X1").Value = Worksheets("Sheet2").Range("X2").Value
Worksheets("Sheet2").Range("X2").Value = Range("X1").Value
End If
End Sub


Thanks again!

Originally Posted by Bernie Deitrick

Just one event, with lots of If/End Ifs

Please Login or Register  to view this content.

RIght, OK that makes sense. I shouldn't have made an example with both A1 and B1 in both sheets though, hard to tell which is which when I'm changing the code to fit. Could you help me with the code for the actual cells? I owe you big time mate

Sheet1
Cell C23 = 100
Cell C24 = N/A

Sheet2
Previous B2 = blank
Current B3 = 100

-> data changes

Sheet1
Cell C23 = 200
Cell C24 = % change

Sheet2
Previous B2 = 100
Current B3 = 200