excel too slow with calculations

[IraAxa]

Hi there,

i tried to look for the solutions online regarding too slow excel calculations, and i applied all the advices regarding the formula usage, hence my excel once i hit F9 takes for ever to calculate. we are not talking about too much data - a bit over 5000, and the formulas it includes are:

=IF(ISNA(INDEX(Sheet!F:F,MATCH(1,INDEX((Sheet!A:A=Sheet1!A8)*(Sheet!C:C=Date2),),FALSE))),"",INDEX(Sheet!F:F,MATCH(1,INDEX((Sheet!A:A=Sheet1!A8)*(Sheet!C:C=Date2),),FALSE)))

=VLOOKUP(A3,Sheet!A:X,2,0)

=IFERROR((F3-H3)/H3,"--")

there are 2 tabs - Sheet & Sheet1

The Sheet tab pulls data from SharePoint - no calculations involved here - there is Refresh Connection involved , and The Sheet1 uses the calculations from that Sheet. It starts calculating once the Date is selected, but it takes forever.

TiA

 

[ghrain22]

A problem i see that could be causing an issue is you're looking at entire rows. ie. A:A, C:C, F:F, A:X

Excel may be using ALL of the rows in those columns slowing you down(especailly in the case of the vlookup). Try limiting them down. I'm assuming the number of rows either changes or is incosistent. Therefore I would suggest making some dynamic named ranges.

 

[IraAxa]

Great, you saved my day - it workes perfectly; i should have fixed amount data - but just in case i included few blank cells just in case

thanks a lot