Help with maintaining cell history

[MrMac74]

I have a table that tracks contracts and the progress made on these contract. These contract can go through multiple amendments during their lifecycle and I am trying to figure out a way that I could record such amendments in a series of cells (e.g. Amendment #, Amendment date, Amendment Amt). My dilemma is when a contract goes through a 2nd (or 3rd, 4th, etc.) amendment and I change the values of those fields, I lose the previous values. Also, there is a high risk of error that the user will only enter the value of the last amendment and not the cumulative amount (e.g. Amendment #1 + Amendment #2). I don't want to have to add a series of column for each amendment because it will complicate thing when I use this data for reporting (using Pivot). Any insight would be appreciated.
I am using Excel 365 on Windows 10 Enterprise.

 

[Joe4]

Welcome to the Board!

If it were me, I might approach this in a little bit different manner.
Instead of allowing users to update the table directly, I may create a UserForm that they need to enter all the changes into.
Then, you can have an audit/history sheet, where you have VBA code copy over the entire row of the current record to this sheet (so you have record of it), and then have the code apply the changes enter into the UserForm.