Drop post date formula link

I have a budget going wherein debt and income figures are controlled by a master cell, and this allows me to change numbers in one location. e.g. my monthly gas bill is (uh um, was) $200. So I have a cell named "gas" $200. In the gas column is the formula =if(day(b2)=25,gas,""). So if it's the 25th of the month it enters the gas amount $200.

But the cost is changing, and if I change the master gas cell, all cells refereing to it will change, even past dated cells, where the cost is already recognized.

How do I get excel to drop the formula for all cells dated prior to today? In doing so only the amount would be left in the cell, and it would not be linked to any other cell. This way I could manipulate my projected budget without effecting the realized budget.

Thanks in advance.
g.

Hi and welcome to the board.

Another way to achieve this goes as follows :

Select the range to adapt
Put the cursor arrow against the upper boundary of the selected range
While keeping the RIGHT key mouse depressed, move the selected range to where you want ( could be the same place as long as you move around a bit)
After releasing the button a menu will appear
Select " move here as values only"
Finished

( takes longer to explain than to do )

arthurbr,
Your way sounds more inline with what I want to do. I want to edit in place. Thanks

Is this the only way though? I'm surprised that a function like this isn't already in Excel. I would think excel is mostly used for budgets and engineering. Budgets require dates. How do accountants or business managers make quick predictions using current data with out affecting past schedules?

Originally Posted by mtndgz

arthurbr,
Your way sounds more inline with what I want to do. I want to edit in place. Thanks

Is this the only way though? I'm surprised that a function like this isn't already in Excel. I would think excel is mostly used for budgets and engineering. Budgets require dates. How do accountants or business managers make quick predictions using current data with out affecting past schedules?

Well, VBA is also an option. But I'm no good at it so I stick to the easy way
Cheers

Both methods will work. pjoaquin's method can be used to copy and paste special in place. It also already has an icon (little clipboard with a 12 on it) to paste values only. You could create a macro and assign it to a button, icon or shortcut key also.

ChemistB