K
Ken
Hi Group...
I have a spreadsheet that is up to about 8500 rows, and will
continue to grow at about 40 rows per day.....filtering is slow (about
6 - 10 seconds or slower), and I set out this morning to try to find
out why, and now I know why, and I don't know of a way to correct it.
The problem is this: I have a formula in Col N that I have manually
extended to row 50000. When I filter I notice that excel filters down
to the last used row, which is the last row where I extended the
formula to. The formula is this, which by the way was provided by this
group and I'm forever grateful:
=IF(ISNA(VLOOKUP(M8615, CustomerSurveyList, 2, FALSE)),"",VLOOKUP
(M8615, CustomerSurveyList, 2, FALSE)) *(the 8615 is the current
unused row)*
I know that excel will auto extend a formula only if it's present in
the previously used row, but the formula puts a value into the cell in
Col N, and thereby deleting the formula, and thereby not extend the
formula. Do I try to used Col N as a Dynamic Named Range and somehow
tie the formula to that, or what would be the best way to auto extend
the formula only down to the first unused row? Any help or ideas will
be greatly appreciated, and in the meantime, I'll keep looking for an
answer! Thanks to all of you!
Ken
I have a spreadsheet that is up to about 8500 rows, and will
continue to grow at about 40 rows per day.....filtering is slow (about
6 - 10 seconds or slower), and I set out this morning to try to find
out why, and now I know why, and I don't know of a way to correct it.
The problem is this: I have a formula in Col N that I have manually
extended to row 50000. When I filter I notice that excel filters down
to the last used row, which is the last row where I extended the
formula to. The formula is this, which by the way was provided by this
group and I'm forever grateful:
=IF(ISNA(VLOOKUP(M8615, CustomerSurveyList, 2, FALSE)),"",VLOOKUP
(M8615, CustomerSurveyList, 2, FALSE)) *(the 8615 is the current
unused row)*
I know that excel will auto extend a formula only if it's present in
the previously used row, but the formula puts a value into the cell in
Col N, and thereby deleting the formula, and thereby not extend the
formula. Do I try to used Col N as a Dynamic Named Range and somehow
tie the formula to that, or what would be the best way to auto extend
the formula only down to the first unused row? Any help or ideas will
be greatly appreciated, and in the meantime, I'll keep looking for an
answer! Thanks to all of you!
Ken