Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Formulas and Dynamic Named Range (Excel 97)

    I need an array formula containing 3 dynamic named ranges. The formula with a specifically defined range is {=1000*sum(if((mastercode=TEXT(A46,"#######"))*(Pe riod=AI6,Quantity))} returns a correct value. Mastercode, Period, and Quantity are currently specific named ranges, but need to be dynamic named ranges.

    I defined a dynamic named range ... say DMastercode as follows =offset('Worksheet'!$A$2,0,0,Counta('Worksheet'!$A :$A),1) If I substitute the dynamic named range DMastercode in the formula, it returns #N/A. I also created a dynamic named range for DQty but since the first failed I didn't try to substitute it in the formula.

    I tried =sumif(DMASTERCODE,TEXT(A46,"#######"),DQTY). This formula returns a correct value so I know the dynamic ranges are defined properly.

    I need to be able to filter on the 2nd criteria "Period" so I need the Array formula .. but as soon as I used the array formula I get the error.

    Any ideas, suggestions, etc..etc..etc...??? I will be eternally grateful if someone can help get around this.

    Thanks in advance for your time and trouble.

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formulas and Dynamic Named Range (Excel 97)

    are each of the ranges (dynamic or not) the same size? If not, you will get #N/A!

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formulas and Dynamic Named Range (Excel 97)

    Thanks for the reply Bob ... they should be the same size but I will have to double check the definitions ... perhaps I should define the last dynamic range and substitute all three .. and see what happens. I won't be at my clients office again until Tuesday ... and may not have time to look at it until Weds. The file is far to large to bring home. I will post again if that's not the problem. Thanks

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formulas and Dynamic Named Range (Excel 97)

    Thanks again Bob .. that was it ... the dynamic range was NOT picking up the column label and the specifically defined named ranges were .... I was able to create dynamic named ranges for all three variables and it's working like a charm. I appreciate your help!

Posting Permissions

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