Sort code in sheetchange very slow - any improvements possible?

[nickmax1]

As the title suggests, I have made some changes to my worksheets that are sorted (mainly data validation enhancements) and my sort coding below is taking forever to work, often a minute or two for every sheet change event. Any suggestions as to where I can trim some seconds of the time?

Please Login or Register  to view this content.

[nickmax1]

I figured that this code that is present in the above is slowing it down to a crawl - but it is needed, any way this can be improved?

Please Login or Register  to view this content.

[romperstomper]

What formulas are in L17:P17?

[nickmax1]

L17 =IF(OR($F17="New",$A17=""),"",IFERROR(INDEX('P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$AL$3:$AL$8000,MATCH($AI17,'P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$A$3:$A$8000,0)),""))
M17 =IF(OR($F17="New",$A17=""),"",IFERROR(INDEX('P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$AG$3:$AG$8000,MATCH($AI17,'P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$A$3:$A$8000,0)),""))
N17=IF(OR($F17="New",$A17=""),"",IFERROR(INDEX('P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$AI$3:$AI$8000,MATCH($AI17,'P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$A$3:$A$8000,0)),""))
O17= text
P17=IF(I17="","",IF(OR(I17="Quoted",I17="Bound",I17="WIP",I17="Firm"),"Live","Dead"))

[romperstomper]

It's pretty inefficient to do the same MATCH 3 times. You'd be better off doing that in a separate column and then using that value directly in the INDEX formulas. It should improve the speed.

[nickmax1]

romperstomper
thanks for your input - cant seem to change the match formulas now they are pretty ingrained in the spreadsheet. I removed the troublesome code in its entirety and placed it in a seperate module to run ad hoc and its all pretty quick now.

EDIT: Just so you know the match formulas are very similar, but not the same...

[romperstomper]

This part:
MATCH($AI17,'P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$A$3:$A$8000,0)

appears to be the same in all three formulas, unless I'm missing something?

[nickmax1]

you are quite right - but thats beyond my skillset!
If I place the MATCH($AI17,'P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$A$3:$A$8000,0) part in a seperate colomn (AJ17 lets say) that works, but then if I change my original formula to this:

=IF(OR($F17="New",$A17=""),"",IFERROR(INDEX('P:\Departments Online\Property\Activity\[Sublog.xlsx]Page1_1'!$AL$3:$AL$8000,MATCH($AJ17,0)),""))

i get a blank result instead of an expected value.

[nickmax1]

ah figured it out...
=IF(OR($F19="New",$A19=""),"",IFERROR(INDEX([Sublog.xlsx]Page1_1!$AI$3:$AI$8000,AJ19),""))