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.
