Array Formulas and Dynamic Named Range (Excel 97)

Topic

New Reply

I need an array formula containing 3 dynamic named ranges. The formula with a specifically defined range is {=1000*sum(if((mastercode=TEXT(A46,”#######”))*(Period=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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote