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

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

The problem is in using the INDIRECT function.

How many different sheets will you be referencing in B9?

--
Biff
Microsoft Excel MVP


"caroline" wrote in message
...
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

I am referencing 3 sheets
--
caroline


"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

Ok, this may be hard to explain (and undertand)...but here goes...

Let's assume the sheets you want to reference are named:

Sheet2
Sheet3
Sheet4

In your formula you're referencing the 14th column of a range on each of
those sheets. That 14th column is the range N1:N5000

This formula is entered on Sheet1. So, Sheet1 B9 will contain the sheet name
of either Sheet2, Sheet3 or Sheet4.

Create these defined names.
Goto InsertNameDefine

Name: Sheet
Refers to: =MATCH(Sheet1!$B$9,Sheets,0)

Name: Sheets
Refers to: ={"Sheet2","Sheet3","Sheet4"}

Name: Rng1
Refers to: =Sheet2!$N$1:$N$5000

Name: Rng2
Refers to: =Sheet3!$N$1:$N$5000

Name: Rng3
Refers to: =Sheet4!$N$1:$N$5000

Name: SheetRng
Refers to: =CHOOSE(Sheet,Rng1,Rng2,Rng3)

Then, this will replace your current (INDEX(INDIRECT(......)) expression:

INDEX(SheetRng,MATCH(AnalysisItem2,CHOOSE(Sheet,Sh eet2!C:C,Sheet3!C:C,Sheet4!C:C),0)):INDEX(SheetRng ,MATCH(AnalysisItem2,CHOOSE(Sheet,Sheet2!C:C,Sheet 3!C:C,Sheet4!C:C),0)+31)

This will allow you to still copy the formula across and the reference to
C:C will increment as needed.

It's a bit shorter and it gets rid of those volatile function calls.

--
Biff
Microsoft Excel MVP


"caroline" wrote in message
...
I am referencing 3 sheets
--
caroline


"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

Thank you it works a treat!
--
caroline


"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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"caroline" wrote in message
...
Thank you it works a treat!
--
caroline


"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