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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

Just a suggestion - but your problem will likely be resolved quicker if you
don't multi-post. Respondents can then see what has already been suggested
and all pertinent details are in one place for them to consider.

Speaking for myself, I don't respond to questions I know are multi-posts as
it is a waste of time to offer a suggestion that someone else has already
made.



"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

JMB,

thanks for taking the time to type your long message..

shame you could not have helped me out...

"JMB" wrote:

Just a suggestion - but your problem will likely be resolved quicker if you
don't multi-post. Respondents can then see what has already been suggested
and all pertinent details are in one place for them to consider.

Speaking for myself, I don't respond to questions I know are multi-posts as
it is a waste of time to offer a suggestion that someone else has already
made.



"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

Hi Mark

If you check your original thread, I think you will find that JMB has
responded there.
I think he was only replying here to make the very valid point regarding
multiposting.
I don't have any further thoughts on your problem, but if you respond to
JMB's other posting, he or others may be able to help you out.

--
Regards

Roger Govier


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

thanks for taking the time to type your long message..

shame you could not have helped me out...

"JMB" wrote:

Just a suggestion - but your problem will likely be resolved quicker
if you
don't multi-post. Respondents can then see what has already been
suggested
and all pertinent details are in one place for them to consider.

Speaking for myself, I don't respond to questions I know are
multi-posts as
it is a waste of time to offer a suggestion that someone else has
already
made.



"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where
previously
it came from a CSV file.

It has something to do with the format coming in from the database.
If I go
to the data in column L double click on one item and then press
enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

Whether you consider it helpful or not - the fact is some folks simply do not
respond to multi-posted questions, which limits the responses you will
receive.

As it is, your problem (and important info related to/describing) is
fragmented across 3 separate posts which further hinders finding a solution
(it also is generally considered rude as someone may have wasted his/her time
to post a response that was already suggested in another thread).

Chip offers some suggestions on his site:
http://cpearson.com/excel/newposte.htm



"Mark Allen" wrote:

JMB,

thanks for taking the time to type your long message..

shame you could not have helped me out...

"JMB" wrote:

Just a suggestion - but your problem will likely be resolved quicker if you
don't multi-post. Respondents can then see what has already been suggested
and all pertinent details are in one place for them to consider.

Speaking for myself, I don't respond to questions I know are multi-posts as
it is a waste of time to offer a suggestion that someone else has already
made.



"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

thanks for taking the time to type your long message..

shame you could not have helped me out...

He did help... he gave you some very good advice for the future. Please
consider this standard reply that I usually post to multi-posters...

From a post by Jeff Johnson (over in the compiled VB newsgroups):

"You have posted this question individually to multiple groups.
This is called Multiposting and it's BAD. Replies made in one
group will not be visible in the other groups, which may cause
multiple people to respond to your question with the same answer
because they didn't know someone else had already done it. This
is a waste of time.

If you MUST post your message to multiple groups, post a single
message and select all the groups (or type their names manually
in the Newsgroups field, separated by commas) in which you want
it to be seen. This is called Crossposting and when used properly
it is GOOD."

Some additional comment previously posted by me:

"You may not see this as a problem, but those of us who volunteer
answering questions on newsgroups do see it as a problem. You can't
imagine how annoying it is for a volunteer to read a question,
research background material, test sample code and then formulate
and post an answer to the original question only to go to another
newsgroup and find the question posted and ALREADY answered over
there. On top of that, if you cross-post your question, all of the
readers in all the newsgroups it is cross-posted to see both the
original question and all of the answers given to it. This is
beneficial to you because then we can add additional material to,
add clarification to, as well as add additional examples to an
answer you have received previously... that means you end up with
a more complete solution to your problem. This is a win-win
situation for all of us."

Rick