Hi,

I can't make sense of this and I need help see the error.

I have defined two dynamic names.
NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1)
ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1)

Both columns only contain numbers.

I also have another one with dates defined as TrackDate with formula
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1)

So same formula just different range.

Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate=$B$94)*(TrackDate<""),ExistingCa ll) it works
perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate=$B$94)*(TrackDate<""),NewCall) it gives a #value
error.

The problem is NewCall, because if I delete it from the formula it
does return a result.
This is where I am stumped, I can't figure out whats wrong with
NewCall.
in the Define Name window, when the refers to box is selected it
highlits the range in the worksheet. The only difference between
newcall and Existingcall is that existingcall only has one empty row
at the bottom and NewCall has two rows.

How can I troubleshoot this?

I just found out something.
NewCall works if using the formula =OFFSET(Data!$AB$3,0,0,COUNTA(Data!
$AC:$AC),1)

Does it means that theres something wrong with AB rows.
To check that the Offset formula is working, if I use CountA(newcall)
and counta(existingcall) they both give the same result, which is
correct.
I also checked the row format to make sure it is set up as general.
Any ideas?

You should base the height of all the ranges on a "key" column. It sounds
like your DATE column should be the key column.

So:

TrackDate
Refers to:
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA))

NewCall
Refers to:
=OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AA:$AA))

ExistingCall
Refers to:
=OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AA:$AA))

That way each range is based on the height of TrackDate


--
Biff
Microsoft Excel MVP


wrote in message
...
Hi,

I can't make sense of this and I need help see the error.

I have defined two dynamic names.
NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1)
ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1)

Both columns only contain numbers.

I also have another one with dates defined as TrackDate with formula
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1)

So same formula just different range.

Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate=$B$94)*(TrackDate<""),ExistingCa ll) it works
perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate=$B$94)*(TrackDate<""),NewCall) it gives a #value
error.

The problem is NewCall, because if I delete it from the formula it
does return a result.
This is where I am stumped, I can't figure out whats wrong with
NewCall.
in the Define Name window, when the refers to box is selected it
highlits the range in the worksheet. The only difference between
newcall and Existingcall is that existingcall only has one empty row
at the bottom and NewCall has two rows.

How can I troubleshoot this?

Thanks for the help.

It makes sense, and I will do that.
But I still dont understand why AB does not work?

On Jan 29, 1:56*am, "T. Valko" wrote:
You should base the height of all the ranges on a "key" column. It sounds
like your DATE column should be the key column.

So:

TrackDate
Refers to:
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA))

NewCall
Refers to:
=OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AA:$AA))

ExistingCall
Refers to:
=OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AA:$AA))

That way each range is based on the height of TrackDate

--
Biff
Microsoft Excel MVP

wrote in message

...



Hi,

I can't make sense of this and I need help see the error.

I have defined two dynamic names.
NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1)
ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1)

Both columns only contain numbers.

I also have another one with dates defined as TrackDate with formula
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1)

So same formula just different range.

Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate=$B$94)*(TrackDate<""),ExistingCa ll) it works
perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate=$B$94)*(TrackDate<""),NewCall) it gives a #value
error.

The problem is NewCall, because if I delete it from the formula it
does return a result.
This is where I am stumped, I can't figure out whats wrong with
NewCall.
in the Define Name window, when the refers to box is selected it
highlits the range in the worksheet. The only difference between
newcall and Existingcall is that existingcall only has one empty row
at the bottom and NewCall has two rows.

How can I troubleshoot this?

But I still dont understand why AB does not work?
The only difference between newcall and Existingcall
is that existingcall only has one empty row at the
bottom and NewCall has two rows.

All of your ranges might not be the same size (height).


--
Biff
Microsoft Excel MVP


wrote in message
...
Thanks for the help.

It makes sense, and I will do that.
But I still dont understand why AB does not work?

On Jan 29, 1:56 am, "T. Valko" wrote:
You should base the height of all the ranges on a "key" column. It sounds
like your DATE column should be the key column.

So:

TrackDate
Refers to:
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA))

NewCall
Refers to:
=OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AA:$AA))

ExistingCall
Refers to:
=OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AA:$AA))

That way each range is based on the height of TrackDate

--
Biff
Microsoft Excel MVP

wrote in message

...



Hi,

I can't make sense of this and I need help see the error.

I have defined two dynamic names.
NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1)
ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1)

Both columns only contain numbers.

I also have another one with dates defined as TrackDate with formula
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1)

So same formula just different range.

Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate=$B$94)*(TrackDate<""),ExistingCa ll) it works
perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate=$B$94)*(TrackDate<""),NewCall) it gives a #value
error.

The problem is NewCall, because if I delete it from the formula it
does return a result.
This is where I am stumped, I can't figure out whats wrong with
NewCall.
in the Define Name window, when the refers to box is selected it
highlits the range in the worksheet. The only difference between
newcall and Existingcall is that existingcall only has one empty row
at the bottom and NewCall has two rows.

How can I troubleshoot this?

Thats what I thought, but using Counta gives me the same result for
either AB and AC, in this case 20.
I dont understand this problem.

Hi

What do you get if you try COUNT() on each of the ranges?
You say the ranges only contain numeric values, but you also talk about
ExistingCall having 1 empty row and Newcall having 2 empty rows.
If they are numbers, and they are truly empty, then you wouldn't get a
result of 20 in all cases, you would get 20, 19 and 18.

I suspect the problem lies with the content of those supposedly "empty"
cells.

As Biff says, in any case, it is always better to use what you would expect
to be the longest column of data as your count, and to use that for all
ranges to get a consistent length.
I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an
example)
then each range would be defined as
=$AA3:INDEX($AA:$AA,lr)
=$AB3:INDEX($AB:$AB,lr)
etc.

This method also avoids the use of the volatile Offset function.
--

Regards
Roger Govier

wrote in message
...
Thats what I thought, but using Counta gives me the same result for
either AB and AC, in this case 20.
I dont understand this problem.