I am trying to re-create a spreadsheet into a single file where previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this should not
matter.

Please help as this is really frustrating !!!!

Mark

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
Are they numbers or text? Are the numbers true numbers or Text numbers
compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark

Roger please see answers below:

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
**A3 and A19 are on the calculation sheet S Wright. This is where I start
the equation from

Are they numbers or text? Are the numbers true numbers or Text numbers
** There is both numbers and text. ie. A3=Suzanne Wright and A19=CPS900

compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.
** NO

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark

Hi Mark

Try it as
=SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19))

Does that make any difference?

--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Roger please see answers below:

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
**A3 and A19 are on the calculation sheet S Wright. This is where I
start
the equation from

Are they numbers or text? Are the numbers true numbers or Text
numbers
** There is both numbers and text. ie. A3=Suzanne Wright and
A19=CPS900

compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.
** NO

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark

Roger,

this is something to do with the cell format for Data on L$1:L$10000 if I go
to one that is just a number edit and press return it then calculates !!!

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
Are they numbers or text? Are the numbers true numbers or Text numbers
compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark

No still the same !!

"Roger Govier" wrote:

Hi Mark

Try it as
=SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19))

Does that make any difference?

--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Roger please see answers below:

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
**A3 and A19 are on the calculation sheet S Wright. This is where I
start
the equation from

Are they numbers or text? Are the numbers true numbers or Text
numbers
** There is both numbers and text. ie. A3=Suzanne Wright and
A19=CPS900

compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.
** NO

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark

Hi Mark

When you copied the data from the other workbook, did you Paste or Paste
SpecialValues?
If there are formulae sat in column L and they haven't been evaluated,
then may this workbook has its calculation mode set to Manual.
ToolsOptionsCalculationAutomatic

--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Roger,

this is something to do with the cell format for Data on L$1:L$10000
if I go
to one that is just a number edit and press return it then calculates
!!!

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
Are they numbers or text? Are the numbers true numbers or Text
numbers
compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark

Hi Roger, are you still there ??

Have you any more suggestions ??

Mark

"Roger Govier" wrote:

Hi Mark

Try it as
=SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19))

Does that make any difference?

--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Roger please see answers below:

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
**A3 and A19 are on the calculation sheet S Wright. This is where I
start
the equation from

Are they numbers or text? Are the numbers true numbers or Text
numbers
** There is both numbers and text. ie. A3=Suzanne Wright and
A19=CPS900

compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.
** NO

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark

see response to your 16:29 post about column L


--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Hi Roger, are you still there ??

Have you any more suggestions ??

Mark

"Roger Govier" wrote:

Hi Mark

Try it as
=SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19))

Does that make any difference?

--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Roger please see answers below:

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
**A3 and A19 are on the calculation sheet S Wright. This is where I
start
the equation from

Are they numbers or text? Are the numbers true numbers or Text
numbers
** There is both numbers and text. ie. A3=Suzanne Wright and
A19=CPS900

compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.
** NO

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in
message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but
this
should not
matter.

Please help as this is really frustrating !!!!

Mark

Roger,

the data comes from an SQL database straight into a new sheet..

how frustrating.....I am going to throw the lap top out of the window !!!!

Mark

"Roger Govier" wrote:

Hi Mark

When you copied the data from the other workbook, did you Paste or Paste
SpecialValues?
If there are formulae sat in column L and they haven't been evaluated,
then may this workbook has its calculation mode set to Manual.
ToolsOptionsCalculationAutomatic

--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Roger,

this is something to do with the cell format for Data on L$1:L$10000
if I go
to one that is just a number edit and press return it then calculates
!!!

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
Are they numbers or text? Are the numbers true numbers or Text
numbers
compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark

What does the data look like - text, numbers, both? What is in A3 and A19?

Since you can edit a cell, press enter, and get it to work, it sounds like
the data is supposed to be numeric, but is coming in as text.

Try copying a blank cell, then select your range of data (that I assume is
supposed to be numeric) and click edit/paste special/Add. Does that do
anything?



"Mark Allen" wrote:

Roger,

the data comes from an SQL database straight into a new sheet..

how frustrating.....I am going to throw the lap top out of the window !!!!

Mark

"Roger Govier" wrote:

Hi Mark

When you copied the data from the other workbook, did you Paste or Paste
SpecialValues?
If there are formulae sat in column L and they haven't been evaluated,
then may this workbook has its calculation mode set to Manual.
ToolsOptionsCalculationAutomatic

--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Roger,

this is something to do with the cell format for Data on L$1:L$10000
if I go
to one that is just a number edit and press return it then calculates
!!!

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
Are they numbers or text? Are the numbers true numbers or Text
numbers
compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark