hello,
I am using a lot of sumproduct functions which are using the same range as
last item.
the range is defined by a rather complicated formula as I need a lot of
variables:
(INDEX(INDIRECT("'" & $B$9 &
"'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRE CT("'" & $B$9 &
"'!C:C",TRUE),0),14):INDEX(INDIRECT("'" & $B$9 &
"'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRE CT("'" & $B$9 &
"'!C:C",TRUE),0)+31,14))

example of SUMPRODUCT function I am using:
SUMPRODUCT((Input!$C$16:$C$47="Yes")*(Input!$E$16: $E$47="nonLCM")*(INDEX(INDIRECT("'"
& $B$9 & "'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRE CT("'" & $B$9 &
"'!C:C",TRUE),0),14):INDEX(INDIRECT("'" & $B$9 &
"'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRE CT("'" & $B$9 &
"'!C:C",TRUE),0)+31,14)))

It works fine but the calculation is very slow.
i thought that a way round would be to define the range once. for instance
in A1 writing (INDEX(INDIRECT etc........and then reference it in all my
SUMPRODUCT functions
SUMPRODUCT((Input!$C$16:$C$47="Yes")*(Input!$E$16: $E$47="nonLCM")*A1)

How can I do that?
--
caroline