#REF errors on formula laden sheet when data on the referenced sheet moves...??

This is is a one time use, brute force, and ignorance approach to meeting my needs but I'm not a programmer so a more elegant solution is not an easy task....

I have a workbook with 2 worksheets. Sheet1 contains transactional data for entities. For each entity on sheet1 there are 7 rows of data. The next 7 rows on sheet1 are for the second entity and so on down sheet1. Sheet2 contains a bunch of formula cells that reference and aggregate the data for the first entity(top 7 rows) on sheet1. My plan is to copy the aggregated data off sheet2 for the first entity into document A, then delete the first 7 rows of data from sheet1 to move the data for the second entity into the top 7 rows on sheet1, recalc sheet2 to aggregate the data for the second entity, copy the aggregated data for sheet2 into document A....and so on down through the entity data in sheet1.

When I do the row deletions from sheet1 I get #REF errors in sheet2 even though new data moved into the sheet1 rows that are referenced by the formulas on sheet2. How can I change the formula's in sheet2 so they don't #REF?

This is a design decision by the Excel designers, and usually is exactly what the user wants, except in cases like yours. Even if you use absolute references like this

=Sheet1!$A$1

if the first row of Sheet1 is deleted, this formula will be modified by Excel to be

=#REF!

The way around this is to use the INDIRECT function.

The following formula in row 1 of Sheet2 will always refer to whatever is in A1 of Sheet1, no matter what is deleted form Sheet1:

=INDIRECT("Sheet1!$A$"&ROW())