Adjust Range in Function
 

I'm using the following formula to query a worksheet where rows are
added/deleted daily. Is there a way to change the formula so that the range
will auto adjust rather than using $A$1:$A$5001. I just picked this range at
random because I'm already at 3000 rows but I'm thinking that this may be
what's slowing down the calculation process. Thanks.

=SUMPRODUCT(--(masterlist.xls!$A$2:$A$5001-DAY(masterlist.xls!$A$2:$A$5001)+1=DATE(YEAR(J$1), MONTH(J$1),1)),--((RIGHT(masterlist.xls!$B$2:$B$5001,3)=$J$3)+(RIGH T(masterlist.xls!$B$2:$B$5001,4)=$K$3)),--(LEFT(masterlist.xls!$T$2:$T$5001,1)=$J$2),--(ISNUMBER(SEARCH($N$2,masterlist.xls!$AL$2:$AL$500 1))))

Adjust Range in Function
 

Here's something I use in one of my spreadsheets, you should be able to
modify for your purposes:
Named Formulas:
JobTitle =OFFSET(Applicants!$P$3,,,COUNTA(Applicants!$P:$P)-1)
sort_area =OFFSET(Applicants!$A$2,,,COUNTA(Applicants!$A:$A) ,COUNTA(Applicants!$2:$2))
Store =OFFSET(Applicants!$L$3,,,COUNTA(Applicants!$L:$L)-1)
UpdDate =OFFSET(Applicants!$A$3,,,COUNTA(Applicants!$A:$A)-1)

Never mind sort_area, it is not used in the formula:
=SUMPRODUCT(--(Store=$A6),--(JobTitle=H$2), --(UpdDate=$J$4))


"pomalley" wrote:

I'm using the following formula to query a worksheet where rows are
added/deleted daily. Is there a way to change the formula so that the range
will auto adjust rather than using $A$1:$A$5001. I just picked this range at
random because I'm already at 3000 rows but I'm thinking that this may be
what's slowing down the calculation process. Thanks.

=SUMPRODUCT(--(masterlist.xls!$A$2:$A$5001-DAY(masterlist.xls!$A$2:$A$5001)+1=DATE(YEAR(J$1), MONTH(J$1),1)),--((RIGHT(masterlist.xls!$B$2:$B$5001,3)=$J$3)+(RIGH T(masterlist.xls!$B$2:$B$5001,4)=$K$3)),--(LEFT(masterlist.xls!$T$2:$T$5001,1)=$J$2),--(ISNUMBER(SEARCH($N$2,masterlist.xls!$AL$2:$AL$500 1))))