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

  • Thread starter Thread starter Morgan
  • Start date Start date
M

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
 
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
 
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?
 
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
 
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
 
thanks Otto



Otto Moehrbach said:
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



.
Click to expand...
 
Back
Top