Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help forming a list | Excel Discussion (Misc queries) | |||
Data Validation lists update orginal cell with list update | Excel Worksheet Functions | |||
Forming a trend line | Excel Worksheet Functions | |||
Forming a binary column from a more complicated column of diagnostic codes | Excel Discussion (Misc queries) | |||
forming a new column by using the other column | Excel Worksheet Functions |