update the same cell into another column forming a list on opening

[Morgan]

hi there,
i have made a spreadsheet to enter data and then view the resulting
statistics that are generated, i have one cell that changes in value after
entering the data, and i would like to keep a record of what values this cell
has been, i was after a formula that would update that single cell's value
into a column forming a list each time the spreadsheet was opened or the
value in the cell changed, like a field perhaps, this would fill up the
column with a list of results from which to generate more stats and a graph
etc.

any help would be great, thank you

 

[Otto Moehrbach]

This macro executes whenever the active sheet is calculated. The macro
looks at F1 of the active sheet and compares it to the last entry in Column
A of a sheet named "Record". If the two values are not the same, the macro
places the value in F1 of the active sheet into the first blank cell in
Column A of the "Record" sheet. Note that nothing is done if the 2 values
are the same. Date and/or time values are not placed in "Record" because
you didn't mention wanting that. This macro is a sheet event macro and must
be placed in the sheet module of the sheet with the changing F1 cell. You
can access that module by right-clicking the sheet tab and selecting View
Code. HTH Otto
Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("Record")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If Range("F1") <> Dest Then _
Dest.Offset(1) = Range("F1")
End With
End Sub

 

[Morgan]

thanks it works great, you mentioned the date and it sounded like a good
idea, what would the code be to have the date in column A and the entries in
column B?

 

[Otto Moehrbach]

Note that I chose a date format of 14-Mar-09. That is just my preference.
You can record a macro and format a cell to your preference and change the
code accordingly. The date format comes after "NumberFormat" in the code.
Otto
Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("Record")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If Range("F1") <> Dest Then _
Dest.Offset(1) = Range("F1")
Dest.Offset(1, 1) = Date
Dest.Offset(1, 1).NumberFormat = "d-mmm-yy"
End With
End Sub

 

[Otto Moehrbach]

Sorry about that. I didn't catch the sequence you said (date in A and entry
in B). The following works like you want. Otto
Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("Record")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If Range("F1") <> Dest Then _
Dest.Offset(1, 1) = Range("F1")
Dest.Offset(1) = Date
Dest.Offset(1).NumberFormat = "d-mmm-yy"
End With
End Sub

 

[Morgan]

thanks Otto

Sorry about that. I didn't catch the sequence you said (date in A and entry
in B). The following works like you want. Otto
Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("Record")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If Range("F1") <> Dest Then _
Dest.Offset(1, 1) = Range("F1")
Dest.Offset(1) = Date
Dest.Offset(1).NumberFormat = "d-mmm-yy"
End With
End Sub



.