I apologize if this has been answered elsewhere already, but I must be using the wrong words as I cannot seem to find a solution.



I have been creating a "Budget Calendar". In one tab I have a calendar with space in each day for items and their amounts (it is an adaption of an expenses calendar template I found online). In a second tab, I have listed all my regular incomes and expenses. Every time an income or expenses is due on the calendar, it references its appropriate value in the tab with the expected income and expenses. I currently use the INDEX formula to do this. This works fine for now.

My soon-to-be problem is when the expected incomes or costs change (as they might from month to month), if I change the values in the Expected income and costs tab, it updates for all the previous times it has been referenced in the calendar tab. This then ruins my budget, as I have monthly rolling calculations that are then thrown out of whack. I need my formula to copy this data over and make it static and not reference the cell again, sort of how you would in a database that deals with invoices. (As your price for an item changes, you don't want the previous issued invoices to update to new prices as that isn't what was charged at the time).

How might I go about this?

Feedback much appreciated in advance.

Hi and welcome
please post a sample sheet (no pics please) with some data and manually mocked up results. Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

On fly. I'd have in Incomes table a column, where you indicate from which date this entry for this income article is valid (the entry is valid from this date to next entry for same income article (by date), or to current time when there is no more entries for this article. And probably a column, where all table rows are numbered (a formula like =ROW()-X). So every income article can have many rows in Incomes table.
In calendary, you have to use SUMIFS instead of INDEX to get searched values, or to get the row number of income article entry in Incomes table, and then use it in INDEX function.

This requires either the use of keyboard shortcuts ... or using the mouse and clicking menu selections. You can also create a macro that, once the affected cells are selected, will convert all those cells at once.

Not tested here :