Keeping Old Values upon re-calculating

[Captain Brian]

I have a spreadsheet that calculates fuel costs as a function of hourly fuel consumption x price per gallon. As the price per gallon changes, I change the cell that contains the fuel price. The problem is, all the previously calculated fuel expenses change when they should be fixed at that point in time, with the particular fuel price at that time. Is there a way to keep the older values from changing on recalculation and still change new values?

[JBeaucaire]

Use a DROP DOWN to enter fuel prices.

1) Select a single cell where you input the current fuel price. Let's assume it's Z1.

2) Now click on Z1, then type a new NAME for that cell in the Name Box, just to the left of the formula bar. The Name Box probably is displaying the cell's address: Z1. Click on that box and let's enter the name FuelPrice (one word, no spaces)
http://screencast.com/t/x2QKCqxlEN

3) Now select the column of cells where the Fuel Prices are entered for each row.

4) Open the Data > Validation settings for those cells and apply these settings:

Allow: List
Source: =FuelPrice
http://screencast.com/t/wGid020hmy

5) now you can click on any of those cells and the drop down will present the "current" fuel price. Clicking that price will enter that value into the cell as a flat value, not a formula. Those entries will not change when you change the FuelPrice cell, unless you use the drop down again on those same cells.

http://screencast.com/t/kGtwPvlUC4s

[Captain Brian]

I appreciate your help but I think my situation is a little different than you think. I have a column of cells. Each cell calculates the cost of fuel used during a flight by a simple formula (hours flown*fuel consumption per hour*(fuel price/gallon). The cell that contains the fuel price/gallon ie: $t$2, is used in the formula as a absolute reference in place of entering the fuel price into the formula. (This makes it easier for the secretary to just change the cell that says "fuel price"(T2). Is there a way to accomplish this idea without recalculating all the past "cost of fuel". Can the value in the cells be protected once a calculation is done so the past values will be accurate with the past price per gallon and not the current price per gallon?

[JBeaucaire]

My technique would work and should be considered. You're filling out rows of "single instance" data. On that row should be the spot that holds teh value of fuel on that day. You can type than in manually or use the drop down trick, but the cell needs to be a non-changing value for the formula you have to not change later.

==============

Alternate suggestion would be to highlight these formulas and "flatten" the values, removing the formulas.

Copy > Paste Special > Values