fixed cost monthly that may change used in running balance not changing past data

[AkaTrouble]

hi, wasn't sure how to title like Google as don't thin google an help on this one

easy to ask question but difficult to find solution

i have running balance sheet and a master fixed price sheet (master, balance)

on the balance sheet i have a historic running balance for all income and outgoings one day per line/row with running balance auto-calculated from yesterday balance plus/minus today's transactions

in order to prevent errors and offer some automation i created a master sheet that holds monthly direct debit information which is automatically called to a column on the balance sheet.

this all works ok so far then the problem presents itself. on of the monthly direct debit amounts changes, no problem just change the amount on the master page. yes this works going forward but as the balance sheet has called this figure by formula for the previous months these also change making their value = to the new value forcing the running balance to recalculate wrongly.

this can easily be overcome going forward by manually entering a start balance each month, but you cannot carry out any historic calculation or analysis as the figures are all wrong.



thoughts, ideas, solutions ??? PLEASE ...


thanks for reading

regards

sample file enclosed

[Brendan_Floyde]

Once the balance sheet is reconciled at a given date you should paste value the formulas up to that date to prevent future changes - and hence solve your issue?