Row calculation changes each month when data from previous month is deleted

Excel 2007

I volunteer for a food pantry and am trying to us the data we collect each month to automatically generate the numbers for the reports we are required to submit to the Food Bank in order to keep our association with them.

I have 12 sheets in the workbook, one for each month. All the formulas work correctly and generate the numbers. I have a 13th sheet with 12 report "blocks" on it and a 14th sheet with vlookup data on it. As the client data is entered the numbers change at the top of the monthly sheet and also on the report page in the corresponding monthly block. So far, so good, the numbers for the reports are generated.

Originally I had it calculating the numbers from rows 6 through 2000 in each column.

The problem that has risen now is at the the end of each month, the ladies, in an effort to keep from typing so much of the same data each month would like to keep the previous month data on the new month sheet. Most of the time for a returning client just the date is changed to the corresponding month.

For example, lets say you enter that data for January, its no problem. If you copy the sheet and rename it February and add the data for new clients for the month of February, you then have both months data on one sheet which is still not a problem, as you sort by date and delete the January data to give you the February report. Before the January data is deleted from the February sheet it is then and renamed March. Now you have 2 months of data on March sheet.

To keep it manageable you sort by date and then delete the January data on the March sheet and that's where the problem comes in. When the January data is deleted from the March sheet, the cells that are calculated are reduced by the number of rows deleted. Originally it was calculating rows 6 thru 2000 but if you delete 500 rows of January data from the March sheet the formula changes to only calculating rows 6 thru 1500. Each month the numbers are reduced at some point it will quit calculating unless I go in and redo the formula to it's original state.

Is there a way to keep the number of rows constant in the calculation even when you delete some on the next moths sheet? In other words, keep rows 6 - 2000 or would it just be easier to increase the number of rows in the calculation to 20,000 or 30,000 so the formula doesn't run out as they delete?

I've tried various ways but haven't come up a more "elegant" solution.

I can suggest PivotTable but without seeing workbook that's all what I can say