Index formula with large range is very slow

I'll try not to make this explanation too long, but I'm not sure how much info is needed.

The formulas below span 10 columns. The range in which the formulas are contained is Z12:AI2000:

So, Z12's formula is:

=IFERROR(INDEX(O:O,SMALL(INDEX(ROW(O$12:O$2000)*(O$12:O$2000<>""),0),ROWS($12:12)+COUNTBLANK(O$12:O$2000))),"")

and it goes to AI12's formula which is:

=IFERROR(INDEX(X:X,SMALL(INDEX(ROW(X$12:X$2000)*(X$12:X$2000<>""),0),ROWS($12:12)+COUNTBLANK(X$12:X$2000))),"")

This spans all the way down to Z2000:AI2000


The range O12:X2000 is referencing the range B12:K2000 where data is entered. In cell K12, if I enter a 1, then the data from B12:K12 is carried to O12:X12. If K12 is blank, then the O12:X12 remains empty. The same is true for all rows down to K2000. Hope all that's clear, but I doubt it!

The role of range Z12:AI2000 is to weed out any blank rows from O12:X2000. It takes a very long time to tab from one cell to the next in the cells where data is entered due to the calculations happening in Z12:AI2000.

The question is, is there a more efficient way to accomplish this without slowing everything down?

So...nothing?

I usually avoid the INDEX(SMALL(INDEX construct, as it can be very slow for large ranges. Instead, you can use a helper column which applies the condition and allocates a sequential number to records that match the condition. Then it is relatively easy to use an INDEX(MATCH(ROWS formula to retrieve the data.

The helper formula would be something like:

=IF(O12="","-",COUNTIF(O$12:O12,">0))

which is then copied across 10 columns and down to row 2000.

It would help if you attached a sample workbook, then I can show you how to set the formulae up.

Hope this helps.

Pete