Using Indirect with Array formulas (Excel2003)

[zeddy]

I have this array formula:

{=IF(ISNA($N$5),"-",SUM((INDIRECT($N32)=$E32)*(INDIRECT($N$7)=O$13)) )}

When the values for $N32 and $N$7 change, the formula does not re-calculate automatically as expected.

If I press [F2]-Edit and then [Ctrl][Shift][Enter] to re-enter the array formula, it re-calculates correctly.
If I copy the formula into a cell, it it re-calculates correctly.

Is it just one of those Excel quirks or is it me???
I have checked that calc mode is set to automatic.
I have checked that the specific sheet has EnableCalculation set to True.

Any ideas??

zeddy

[rory]

Hi,
What is in the two cells that are within the INDIRECT functions? Are they cell references or range names?

[zeddy]

Hi Rory

Cell $N32 would have a formula which returns a result something like
priorYear1!FW7:FW170

Cell $N$7 would have a formula which returns a result something like
priorYear1!FH7:FH170


zeddy

[rory]

That works fine for me. Does it help if you change your formula to this (non-array) formula:
<code>=IF(ISNA($N$5),"-",SUMPRODUCT((INDIRECT($N32)=$E32)*(INDIRECT($N$7) =O$13)))</code>?

[zeddy]

Hi Rory

Excellent suggestion!
I will be trying that out later.
I will report back in a day or so.

Many thanks

zeddy