Changing reference, saving original calculations

**jorjacman** · 01-24-2014, 03:40 PM

Hello!

Attached is a spreadsheet for reference:

ChangingReference.xlsx

I have a spreadsheet that has a worksheet with a formula that refers to another worksheet through index/match. Currently, if you type in the number of miles and level (both made up things), the cost will be calculated based on the cost per mile in the other worksheet. However, if you change the values in 'Cost per Mile', it changes all of the calculations that already exist.

Is there a way to have the original calculations remain the same when the cost per mile is changed? And have the cost per mile change only affect the calculations AFTER the cost per mile is changed?

Thanks!

**daffodil11** · 01-24-2014, 04:19 PM

What you're touching on is called a Circular Reference, but you'd need to another criteria to match against.

For example,

Please Login or Register  to view this content.

The formula in B2 copied down would check against today's date against the date in A, and if true SUM A11:A13. For cells above and below that, the formula still calculates whatever the cell started at. Maybe yesterday's sum was 16. Today's sum appears to be 6. When the date changes tomorrow, the values in B1 and B2 do not update. You could even delete A11:A13 tomorrow and the values in B1:B2 would not revert to zero.

When intentionally setting circular references, you need to go to File -> Options -> Formulas and enable Iterative Calculations

**popipipo** · 01-24-2014, 04:40 PM

Or something like this?
The cost change at a certain time.

Changing reference, saving original calculations

LinkBack

Thread Tools

Rate This Thread

Display

Changing reference, saving original calculations

Re: Changing reference, saving original calculations

Re: Changing reference, saving original calculations

Thread Information

Users Browsing this Thread

Similar Threads

Changing reference, saving original calculations

creating a copy of a file but not saving original

Saving re-useable template with new name and preserving original copy for others to use

Excel 2007 : Saving the original version of invoice

[SOLVED] Lost my original spreadsheet by saving over it with the same name

Bookmarks

Bookmarks

Posting Permissions