Is there a way to set a cells value based on the value this cell held at a certain time, even when the cell value changes over time?
I have a table in which I track payments of people, working with a TRUE/ FALSE system based on the fact if they have paid or not. Over time, the value of the original cells will change, when a new payment is required, around quarterly with an irregular value of money. When this happens, I would like the cell value to change to how much they have left to pay, while keeping track of the total amount of money they have already paid.
I thought a way might be to freeze the value of the cell based on the date that this cell had at a certain time based on dd/mm/yyyy, but I do not know if this is possible.
Another way I thought of is to have this TRUE/FALSE system in place, with a TRUE value meaning the payment due gets copied to another cell, the problem is however, that if the original value changes, the TRUE/FALSE validation is then to be changed, meaning the value changes as well. If there is a possibility to somehow make that if TRUE is formatted, the value automatically gets copied, without the value being receptive to the validation changing afterwards.
If there are any other suggestions, I would also be open to those.
From what you're describing I wouldn't recommend VBA to overwrite their existing values, but instead recommend having data that stores different data with purpose. You can have a table of their original balance that you will pull in. Then another table of all their payments that you can sumifs. Lastly a summary table that brings in all the relevant info and calculates the outstanding balance.
Figure this is a very simple example, you can add dates, unique IDs, filter by date, add interest, whatever. You can even bring the outstanding balance back in on an "as of" date you use to filter the most recent balance which sounds closer to what you want, without losing the original values in case there's an error.
This picture shows the basic concept of the 3 tables, you can even feed the first two into a data model and handle this all within pivot tables. If you use named tables ( CTRL + T ) you can even avoid having to reference the tables has the expand. The formula text just shows what is happening in the first row of each of the columns.
Here's an example with the named ranges being used and how those formulas would look. (table names are above the tables)
