Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fetching multiple lines in Excel
Hi,
I am using a formula in a sheet which will display multiple lines in excel. However, the only problem is the speed, this formula slows down the performance of the sheet because data size is really huge. I want to know, is it possible to have different formula/way to fasten up the things here. Sample file is uploaded on the following location: http://www.savefile.com/files/1937198 Please help!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fetching multiple lines in Excel
See if maybe these changes don't help you some. They could be tweaked some
more, but it's a potential starting place. First, I used Tools | Options and went to the [Calculate] tab and turned off automatic calculation so I could make these changes without having to wait forever! Deleted all of the formulas from row 3 on down in the Enquiry sheet. Then I modified the formula in cell C2 to read like this (remember that formulas are really one continuous line, not broken up like this board forces them to be at times) : =IF(C1="","",IF(ROWS($1:1)COUNT($B$1:$B$4000),"", INDEX(x!A$1:A$4000,SMALL($B$1:$B$4000,ROWS($1:1))) )) This formula says that if the cell above it is "" then make it "" also instead of going through the long comparisons using Rows, Count, Index and small. Because it has a label in C1, it will work normally - which is good because all of the cells below it are going to get the same treatment. Then I replaced the formula in D2 with this one: =IF(ISNA(VLOOKUP($C2,x!$A$1:$I$25,COLUMN(B1)-COLUMN($A1)+1,FALSE)),"",VLOOKUP($C2,x!$A$1:$I$25, COLUMN(B1)-COLUMN($A1)+1,FALSE)) I then filled that formula on over to the right all the way to column K. Next I took all the cells from C2:K2 and filled them down to row 4000. Then I reset Tools | Options [Calculate] back to Automatic, saved, closed and reopened the workbook. Now instead of taking about 40-45 seconds to open, it takes around 4 or 5. I presume you don't think you'll need more than 4000 entries on this sheet since that's where you'd stopped before. So no sense in using all of the cells in column B to make determinations, just the 4000 potential ones. Similar logic on the x sheet with the Count and Index functions there. Keep things to a minimum. I realize that the table on sheet x is probably going to grow, so you either need to set the range in the VLOOKUP() formulas large enough to take care of future needs for a while, or define that table (x!A1:I25) with a name and then set up a macro that will automatically redefine the range referred to by that range when it changes (good time might be when that sheet is deactivated). And also change the reference to x!$A$1:$I$25 to the name you give the table and your maintenance worries are pretty much over. And you get a big improvement in performance. "abadd0n" wrote: Hi, I am using a formula in a sheet which will display multiple lines in excel. However, the only problem is the speed, this formula slows down the performance of the sheet because data size is really huge. I want to know, is it possible to have different formula/way to fasten up the things here. Sample file is uploaded on the following location: http://www.savefile.com/files/1937198 Please help!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fetching multiple fields data | Excel Discussion (Misc queries) | |||
Importing multiple lines into Excel from txt. | Excel Worksheet Functions | |||
hiding multiple lines in excel | New Users to Excel | |||
Fetching data from another worksheet | New Users to Excel | |||
Fetching External Data from Excel | Excel Discussion (Misc queries) |