Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)))) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
ExceL Options: Add a chart for Box-plot type and Range function | Excel Discussion (Misc queries) | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Number range function | Excel Discussion (Misc queries) | |||
Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions |