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

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

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

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

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


"Otto Moehrbach" wrote:

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

.

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

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

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


"Otto Moehrbach" wrote:

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

.

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

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


"Otto Moehrbach" wrote:

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

.

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

thanks Otto



"Otto Moehrbach" wrote:

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


"Otto Moehrbach" wrote:

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

.

.