auto extend formulas

[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

 

[Shane Devenshire]

Hi,

Could you explain "the formula puts a value into the cell in
Col N, and thereby deleting the formula"? Formulas don't delete formulas,
formula can only return the results of calculations to cell, they can not
change the user interface.

Excel has a feature to automaticlly extend formulas when data is extended,
so depending on your data you don't need a lot of extra formulas below the
last line of data.

Even withstanding that if you are adding 40 rows a day 50000/40 = > 1250
days and assuming you aren't entering dates on weekends and holiday --> 1750
or ~9 years. Are really going to be using this same file for 9 years?

 

[Ken]

Shane you are exactly right about the "delete formulas", I was just
now getting ready to send that if I change the data that's entered
into Col N by deleting and retyping, the formula is gone. The formula
does indeed stay if the formula enters the data. as for the 9 years, I
doubt it, by then somebody else can figure out a better way to do
things, and I'll be gone by then anyway....I'm just looking at the
short term here....I also have "Data range and formula formats"
checked under excel options but the formula does not extend down to
the next row.
Ken