Solved: identify last row and change formula accordingly

[Laurice]

On sheet 1 I have a number of formulas that average the entries in a column on sheet 2 based on specific criteria. The formula has been built to include all the data found in the second through the last row of the column.

My challenge is the number of rows in the sheet change on a fairly regular basis which means that I have to edit the array formulas to reflect the new last row. Any help that you can provide to automate this calculation would be appreciated.

[mikerickson]

Have you looked at dynamic named ranges like

Name: dataRange
RefersTo: =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A$1),1)

[Laurice]

Thanks for the idea. I tried your suggestion although great, my explanation of the situation was not clear. I have attached a copy of the file. On sheet "Summary" cell B20 is the cell in question. Sheet "Assoc listing with pay data" changes every week which means that I have to change the formula in "Summary" B20. Any idea how to automatically have this formula updated to reflect the number of rows in sheet "Assoc listing with pay data"?
Thanks

[mikerickson]

Make three named ranges

Name: EmpCData
RefersTo: =OFFSET('Assoc Listing with Pay Data'!$S$2,0,0,COUNTA('Assoc Listing with Pay Data'!$U:$U)-1,1)

Name: AIData
RefersTo: =OFFSET('Assoc Listing with Pay Data'!$U$2,0,0,ROWS(EmpCData),1)

Name: DateData
RefersTo: =OFFSET('Assoc Listing with Pay Data'!$AD$2,0,0,ROWS(EmpCData),1)

Then the formula in B20 becomes =SUM(IF(EmpCData="FT",(IF(AIData="A",(IF(DataData<>"V",1,0))))))

[Laurice]

Works great. Thank you so much.
Laurice