Forum Discussion
Save Previous Cell Value Of A Changed Cell In Excel
Shelbie1288 mtarler Rajesh_Sinha Would be so kind to update the code with this feature (value of formula not formual)? Also do you know how is it possible to trigger the event with any kind of cell change ?
In my case what I want to accomplish is maintain a log of all the changes, I update cells in column E with a formula and then I want to maintain the old values in columns F, G, H and so on...
And something last, my first 5 rows are headers, is there a way to apply any code starting from row 6?
Many thanks to you all guys for sharing your stardust with us!
If track changes is not a good option and if using cell comments to store the current cell value is acceptable, you could take a look at the attached workbook. The code is in the sheet1 module and the thisworkbook module.
JMB17 Many thanks!!!! This is exactly what I need! Only thing is that the input is not manual but it´s related to another cell which is located to a different worksheet. To be precise I use this worksheet to concentrate data from another 3-4 worksheets. Do you know how can I trigger the event like this?
Also Do you know how can I start inserting the "history" data at the column G and after?
Many thanks again, incredible!!! 😄
There is a worksheet calculate event that can be used. But, you have to go through every cell in your range (Column E) to see which ones changed (excel passes the range that changed to the change event, but the calculate event won't know which cells were recalculated), so there could be some lag issues if you have a lot of cells to go through.
See attached.
And something last, my first 5 rows are headers, is there a way to apply any code starting from row 6?
fadomas313 again, untested, but I included this ability into the code. If you look at my comment on the lines near the top:
Const xSource = "G:G, L:L" 'This is a list of columns that need to be saved Const xTarget = "D:D, H:H" 'This is a matching list of columns where to saveThose should actually be ranges so you should be able to do something like this:
Const xSource = "E6:Z1000" 'This is a list of columns that need to be saved Const xTarget = "F6:AA1000" 'This is a matching list of columns where to saveas for any kind of cell change I don't understand what you mean.
That all said, there is a track changes feature in excel. This is a legacy command and can show you changes in another sheet or as comments. Here is a webinar I bought (actually my employed bought) and watched but I'm sure there are others:
https://www.complianceiq.com/trainings/livewebinar/3707/excel-spreadsheets-ensuring-data-integrity-and-21-cfr-part-11-compliance
