Solved

# excel slow calculation (4 processors)

Posted on 2013-10-26

Hello,

I am trying to build up a workbook and have set up a formula that seems to overload the excel calculation capacity.

the formula is:

=LOOKUP(1,1/(INDIRECT("'"&B2&"'!$H:$H")=A2),INDIRECT("'"&B2&"'!$X:$X"))

=LOOKUP(1,1/(INDIRECT("'"&B2&"'!$H:$H")=A2),INDIRECT("'"&B2&"'!$AA:$AA"))

The cell B2 corresponds to a worksheet in the workbook with the corresponding name. The cell A2 is a reference which the formula uses to find the last row in the worksheet with that reference in column H and then the result is the cell value in column X of that row.

The workbook will have about 20 worksheets with 10 columns and 4000 rows.

Is there a better way to write the formula or create a function in VBA to speed up the calculation process?

Thanks,

CC