Dynamic named ranges and SumProduct function

Posted by tee on July 22, 2001 6:38 PM

Hi Everyone

I have the dynamic named range
VDate
=DB!$A$1:OFFSET(DB!$A$1,COUNTA(DB!$A:$A)-1,0)

VVis
=DB!$P$1:OFFSET(DB!$P$1,COUNTA(DB!$P:$P)-1,0)

I am using the SUMPRODUCT to count the types.

=SUMPRODUCT((MONTH(VISDate)=1)*(VVis="Magistrate"))

Now I am using a dymanic named range and have this error

#VALUE!

Any suggestion on how to fix it or is the SumProduct function not able to be used with dynamic ranges.

Many thanks in advance
Tee

Posted by Aladin Akyurek on July 22, 2001 9:26 PM

Tee,

Make VDate:

=OFFSET(DB!$A$1,COUNTA(DB!$A:$A)-1,1)

Make VVis

=OFFSET(DB!$P$1,COUNTA(DB!$P:$P)-1,1)

PS. How did you come upon the idea of using SUMPRODUCT?

Aladin

Posted by Aladin Akyurek on July 22, 2001 9:29 PM

Oops.. Start over

Tee,

Make VDate:

=OFFSET(DB!$A$1,0,0,COUNTA(DB!$A:$A)-1,1)

Make VVis

=OFFSET(DB!$P$1,0,0,COUNTA(DB!$P:$P)-1,1)

PS. How did you come upon the idea of using SUMPRODUCT?

Aladin

Posted by tee on July 24, 2001 11:43 PM

Re: SumProduct

Not sure how I come by way of this - I have had help for many excel wizards over the last few months.

I have since this posting - found a download of of dynamin range add-in that has help me set up the offset function - it works like a dream. If you want a copy let me know and I will email you the site.

Many thanks for your help on a number of occasions.

Tee

Posted by Aladin Akyurek on July 25, 2001 2:19 AM

Re: SumProduct

As Quine or Duhem would say, such ideas can com from anywhere. Happy to say that you had problems with named dynamic ranges! ;-)

OK. I must say that I use seldom wizards & other types of utilities.

Tee -- One thing though: you may run against performance problems with loads of macro's, add-ins that are not needed for the particular app that you built. So, my advice would be, don't include them if possible.

And thanks for interesting questions.

Aladin