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?