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)+(RIGHT(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$5001))))
Bookmarks