Defining Dynamic ranges

I'm trying to make ranges in a SumProduct formula dynamic, but getting errors #N/A. I think this is because the top two rows are headers, throwing off the range count.
Q. How do I adjust the range definition to compensate., e.g.

Range =Offset(Sheet1!$D$3,0,0,COUNT(Sheet1!$D:$D),1) where column D is numeric, and

Range = Offset(Sheet1!$T$3,0,0,Match("*",Sheet1!$T:$T,-1),1) where column T is text?

I had assumed that the offset value 3 would do this, but I suspect the functions count and match are not doing so.

Thanks for your attention.

Hi

I think you'd need to start Match at A3:Aend to get that to work. Alternatively, adjust the Match return:

Range = Offset(Sheet1!$T$3,0,0,Match("*",Sheet1!$T:$T,-1)-2,1)

Richard

kysaul,

Your request confuses me.
You talk about sumproduct, but I see non. The #N/A result is when you are multiplying (sumproduct) with a #N/A. The #N/A comes up when the result of Match was unsuccesfull.

Next A:Aend is just to say the list is ending, limited like A1:A6. It is not a function.

The result of =Offset(Sheet1!$D$3,0,0,COUNT(Sheet1!$D:$D),1) is a range and not a value. Count() results in a number and that is your new rowsize.

The Match find the last String value and therefore =Offset(...match()..) results in a single cell.

Your Title is "defining dynamic ranges". is this to be used in a chart? If so, please use these examples:
XVALS=OFFSET($D$1,1,0,COUNTA($T:$T),1) Xseries
YVALS=OFFSET($T$1,1,0,COUNTA($T:$T),1) Values
Where $T$1 and $D$1 are the headers.
=SERIES(,Book1!XVALS,Book1!YVALS,1)

Originally Posted by kysaul

Thanks for reply.
The 'A:Aend' gives me a formula error, I'm not familiar with that function.
Adjusting Match return works! Adjusting Count doesn't. Any idea why? Thanks again

When i said A3:Aend I actually meant the equivalent of A3:A65536 (assuming xl2003 or below) or A3:A2000 assuming that encompassed your entire data rows. Match returns a position within the specified range relative to the start of that range whereas count returns the number of numeric cells in the given range - they are performing two different things.

You should be able to use the same MATCH function (using the same range as the match ie column T NOT column D) in place of your count.

Richard

Edit: if you are not getting this to work, please post both your DNR formulas incorporating the above suggestion.

Or better still, if your column T formula using MATCH is called rng1 then base your column D DNR off of it using:

=OFFSET(rng1,0,-15)

I may have the -15 wrong - whatever the offset from colT to col D is.