Speed up workbook calculation

[Rolly_Sefu]

Hello
I have an excel file that is very slow on calculation, due to the followings:

The excel file is a collection of information, all the workers in a departement ( col A, alfabetically sorted ) and a 3 month forecast of the holiday columns ( C-BP (workdays from Jun-Aug) ). and there are 4 types of holidays they can have

A4 => =IFERROR(INDEX(Overview!$B$4:$B$1000;MATCH(SMALL(IF((COUNTIF($A$3:A3;Overview!$B$4:$B$1000)=0)*(Overview!$C$4:$C$1000=$C$1);COUNTIF(Overview!$B$4:$B$1000;"<"&Overview!$B$4:$B$1000);"");1);COUNTIF(Overview!$B$4:$B$1000;"<"&Overview!$B$4:$B$1000);0));"")
'to get the alfabetical list

C4 => =IF(SUMPRODUCT((Holiday!$B$2:$B$10000=$A4)*(Holiday!$D$2:$D$10000<=C$3)*(Holiday!$E$2:$E$10000>=C$3)*(Holiday!$F$2:$F$10000="Holiday"))=1;1;IF(SUMPRODUCT((Holiday!$B$2:$B$10000=$A4)*(Holiday!$D$2:$D$10000<=C$3)*(Holiday!$E$2:$E$10000>=C$3)*(Holiday!$F$2:$F$10000="Holiday2"))=1;2;IF(SUMPRODUCT((Holiday!$B$2:$B$10000=$A4)*(Holiday!$D$2:$D$10000<=C$3)*(Holiday!$E$2:$E$10000>=C$3)*(Holiday!$F$2:$F$10000="Holiday3"))=1;3;IF(SUMPRODUCT((Holiday!$B$2:$B$10000=$A4)*(Holiday!$D$2:$D$10000<=C$3)*(Holiday!$E$2:$E$10000>=C$3)*(Holiday!$F$2:$F$10000="Holiday4"))=1;4;""))))

'get the number for the holiday (Holiday type)
'i needed a sumproduct since in the holiday sheets i have multiple entry's of holiday's / month / person

C4 => also has 4 types of conditional formatting to color the cells according to holiday type
C4 => conditional formating => cell equal to 1 and apply's color

i have added the following code to VBA stop the file from unnecessary calculations.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("C1")) Is Nothing Then
    Sheets("Print").EnableCalculation = True
    Sheets("Overview").EnableCalculation = True
    Sheets("Print").EnableCalculation = False
    MsgBox ("Done")
End If
If Not Intersect(Target, Target.Worksheet.Range("BR2")) Is Nothing Then
    Application.ScreenUpdating = False
    Sheets("Print").EnableCalculation = True
    Sheets("Overview").EnableCalculation = True
    Range("C2:BP2").FillRight
    Set rng = Range("D2:BP2")
    For Each cell In rng
        If cell = "" Then cell.Value = ""
    Next cell
    Set rng = Nothing
    MsgBox ("Done")
    Sheets("Print").EnableCalculation = False
    Application.ScreenUpdating = True
End If
End Sub

C1 => is the department changer
BR2 => is the month changer ( it goes true all the cells to delete the "" cells so that the month name is fully visible )
C2 => =IFERROR(IF(WORKDAY(DATE(YEAR(TODAY());MONTH(C3);1)-1;1)=C3;PROPER(TEXT(C3;"MMMM"));"");"") 'month name
C3 => =WORKDAY(DATE(YEAR(TODAY());MONTH(DATEVALUE($BR$2&" 1"));1)-1;1) 'workdays

With all the calculation it need about 45 seconds to complete.


Any ideas how i can make this faster ???

Thank you.

 

[tonyyy]

Rolly_Sefu,

Just for kicks, try deleting all Conditional Formatting to see if performance improves. If it does, you can delete CF at the start of your macro then add them at the end.

Cheers,

tonyyy

 

[FDibbins]

Another option might be to use some helper columns to do some of the heavy lifting, you may be able to do away with the array formulas

 

[Rolly_Sefu]

Hello

Thank you for the replay's

I can not delete the conditional formatting since that is the major reason of the file.

But I managed to change the sumproduct formula into an index sumproduct sort of formula

=IFERROR(INDEX(Holiday!$F$2:$F$10000;MATCH(1;INDEX((Holiday!$A$2:$A$10000=$A4)*(Holiday!$C$2:$C$10000<=C$3)*(Holiday!$D$2:$D$10000>=C$3);0;1);0));"")

This way I get to have a holiday type in the cell but only if the cell is between 2 dates, and I just change the conditional formatting types to match the result from the formula.

 

[Rolly_Sefu]

I think the major down force of the file is the alphabetically sorted list in column A it has 100 rows