What is the best way to save data that is formula dependent?

[chriswrcg]

This is a general question and not one that needs to end with a formula per say.

I was wondering what the accepted (or best method) for saving data on a worksheet that is formula dependent?

I have a workbook that has a calculation page that takes todays order numbers and other cell values to return a set range of values needed for my business. The "Daily reporting page" will not ever be deleted and will be available to anyone who might want to look back and see what was done on that particular day.

The issue is the calculation page becoming bogged down with old data that the formulas on the reporting page needs to reference. The optimal solution would be to "wipe" the calculation page and start fresh the next day, but all the returned values on the reporting page would #Ref! out.

What is the best way to "preserve" the old data for future use without eventually having a calculation page miles long, or having to create an simple "archive" macro that would copy the whole page and paste as text that inevitably someone would forget to run.

Or are those my only two solutions?

[sweep]

Hi,

In the past I've dealt with "freezing" a monthly record by having a macro that ran automatically as the workbook closed. Each month was stored on an individual worksheet. The macro would compare the names of each worksheet and if previous to the current date, would set all cells to their current value, thereby removing the formulae.

Could you post an example of what you have and what you'd like - perhaps a small amount of code would sort this for you.

[chriswrcg]

It would have to be a macro that checked the cell position on a row and verify value, and if condition is true, then convert formula in cell "blank" to value.

tab 12, column I, if cell value is 0 then convert cells in column J and O to value.

Macro run when cell in column I hits 0

[JohnTopley]

Try

Please Login or Register  to view this content.

Right click on I2 tab, "View Code", Copy/Paste above code