Hi All

I have 5 sheets. 4 of the sheets contain forecast data and 1 of them
contains Actual data.

The forecast sheets have the following formula under the required
columns at the bottom of the data range for calculating the
corresponding actual total when the forecast data is autofiltered:

'=SUM((SpecialLookUp(SubRangeSingleCol(QT_SAM_FCT4 _1, 3), ACT!
QT_SAM_ACT_1, 3, 6)))

Essentially what I am doing here is retrieving the filtered row IDs in
the Forecast sheet and then looking up these rowIds in the Actual data
sheet and then summing these req. values of the Actual data in the
Forecast sheet.

The problem occurs when I filter the Actual data it takes ages for
Excel to re-calculate these lookup formulas in the Forecast sheets.

Any suggestion as to how to make the calculation quicker would be more
than welcome.




Function SubRangeSingleCol(QueryRange As Range, Col As Integer) As
Range

For Each cl In QueryRange.Columns(Col).Cells

If (cl.EntireRow.Hidden = False) Then
If SubRangeSingleCol Is Nothing Then
Set SubRangeSingleCol = cl
Else
Set SubRangeSingleCol = Union(SubRangeSingleCol, cl)
End If
End If

Next cl


End Function

Function SpecialLookUp(IDInputRange As Range, LookUpRange As Range,
IDCol As Integer, ResultCol As Integer) As Range

For Each cl In IDInputRange.Cells
For Each cl1 In LookUpRange.Columns(IDCol).Cells
If SpecialLookUp Is Nothing Then
If cl1.Value = cl.Value Then
Set SpecialLookUp = cl1.Offset(0, ResultCol)
End If
Else
If cl1.Value = cl.Value Then
Set SpecialLookUp = Union(SpecialLookUp,
cl1.Offset(0, ResultCol - IDCol))
End If
End If
Next cl1

Next cl

End Function


Regards

Lars