Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto extend formulas
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto extend formulas
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? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Ken" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto extend formulas
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto extend formula down a column | Excel Worksheet Functions | |||
Extend Formulas | Excel Worksheet Functions | |||
Auto Extend Formulas doesn't work for a particular formula | Excel Discussion (Misc queries) | |||
Extend formulas | Excel Discussion (Misc queries) | |||
Extend formats and formulas | Excel Discussion (Misc queries) |