Hi,

One thing you might try is creating a range name equal to a portion of the
function that is being used repeatedly. Also, exact matches tend to be
rather slow although there are ways to improve them.

The range name idea is similar to the suggestion you are making. In other
words someting that could be calculated once outside the formula and then
referenced should improve the results.

It looks as though

(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))

is fixed onless you are copying the formula to the right? If so you could
define that portion as a range name or put it in a cell and reference it.
I'm not sure if one is faster than the other.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"caroline" wrote:

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