How Do I Save Volatile Data in Excel?

By kmh69 ·
I have an application that is a series of weekly spreadsheets, with data depending previous weeks' values. In simple terms think of each spreadsheet having a cell that adds 1 to last week's value. This week's spreadsheet relies on last week's data, so I must also open last week's spreadsheet. I use manual calculation (calculate sheet) in order to prevent last week's data from updating (since it depends on the previous week). So far, so good. But then when I click Save on this week's spreadsheet, it appears that Excel tries to recalculate all of the cells of all open spreadsheets, causing last week's spreadsheet value to become undefined, which then ripples down to this week's spreadsheet. I've also noticed that results seem to be better using <CNTRL>+S vs. clicking on the Save button (which I thought were supposed to behave the same). Any ideas? Thanks!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

One more piece of Info

by kmh69 In reply to How Do I Save Volatile Da ...

I make all old versions read-only so that I don't accidentally save the Undefined values.

Collapse -

paste special: (x) values

by Walter Bishop In reply to How Do I Save Volatile Da ...

without knowing what exactly this data is

however, previous weeks have passed and shouldn't need value changes
the data values should be set in stone by Saturday Night at 23:59:59

eg. daily / weekly sales info

at the end of the week, make the data permanent so it doesn't update next week when you open the sheet again

select the volatile cells Ctrl+c
right click on the selected cells
paste special
(x) values

this will convert the formulas to the resultant data and make it permanent
now every time you open the sheet the data is the same
and can be referenced during the following week without problems

only the currently active week should have active formulas
all previous sheets should be changed to the static data produced by the formulas

Related Discussions

Related Forums