Managing Formulas in Excel database

[jacob@thepenpoint]

Greetings Everyone,

I have a workbook where production values are entered for different production lines daily. The database is set up to insert a new blank line just beneath the header row with formulas to make some calculations on the production values when entered. The sheet has grown to be 72 columns wide and currently about 4,900 rows deep and there are about 40 formulas per line giving me a total number of formulas of about 196,000 and this number grows each day as production is entered. This number of calculations is slowing down the line insertion macro to the point it takes nearly 8.5 seconds to insert a row using the macro:

Please Login or Register  to view this content.

What I am wanting to accomplish is to pick an arbitrary length of time, say 90 days, to keep the formulas intact then just do a copy pastevalue to convert the formulas to constants but I am at a loss as to how to do this without looping through the entire sheet to evaluate the dates.

I would appreciate any help or suggestions!

Thanks

[Chris 53]

How about not inserting new rows, just add each days values to the bottom of the table. Does away with the whole insert rows macro. You can freeze the panes so that you can see the data at you want. Set any calculations to show results in the area above the frozen panes.

I spent ages trying to work out how to insert rows and move totals down, until I realised that the totals that I was really interested in could be put at the top and new data could be put at the bottom.

[jacob@thepenpoint]

Thanks Chris

I think I will probably do something like that, but my problem is that my calculations are just to get percentages and efficiency from each record. I see from trying to fix the problem that the biggest problem I have with it is my own poor design. I am leaning towards just having room for the data entry only then doing my data manipulation elsewhere.