Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Using Indirect with Array formulas (Excel2003)

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Indirect with Array formulas (Excel2003)

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

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Using Indirect with Array formulas (Excel2003)

    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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Indirect with Array formulas (Excel2003)

    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>?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Using Indirect with Array formulas (Excel2003)

    Hi Rory

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

    Many thanks

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •