View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Otto Moehrbach[_2_] Otto Moehrbach[_2_] is offline
external usenet poster
 
Posts: 1,071
Default update the same cell into another column forming a list on opening

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" wrote in message
...
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
--
thanks