Sorting gives odd results

[Lee]

=SUMPRODUCT(--(CustomerNumberInvoice=Customers!$D3),--(WeekInvoice=$E$1),INVNET)
I am using the above to compare weekly customer sales of 2008 vs 2009. I
pull the info from a SQL server query for each salesperson's customer info.
The Names are created using offset() and counta(). The problem occurs when I
go to sort the data based on the formula's result it gives the customer's
name correct but the info is all wrong. What am I doing wrong?
Thanks for any help.

I want to say that the people in here have helped and taught me how to do
most of the above and thanks to all.

 

[Khoshravan]

Is there any gap (blank columns) between your table and sumproduct columns?

 

[Lee]

No, there are no gaps. I use the 1 row so the user can input the variables
to view the sales info for the weeks in question. I freeze the window pane
at cell E3.
The sort puts the customer name in the correct position but the sales
information is not correct so for example, the customer in number 1 position
should have $D3 but has $D27 which is the position it used to be before the
sort.
Thanks again,
Lee

 

[Roger Govier]

Hi Lee

It sounds as though you are only selecting the column with the formulae to
Sort.
You need to select the whole table, but sort ascending just on the column
with your values.
That way, the Customer name will move, as well as their result.

 

[Lee]

No, I select the entire table. The customers line up in descending order on
the sort but the data pulls from were the customer used to be in the lineup.
I think there is some absolute value that is not letting it sort properly. I
deleted the $D and replaced it with D but got the same results. Don't know
about the Dynamic Names but don't think there is a problem there.
I have the Query add the formulaes on updates??? but I am sorting not
updating. I can Copy|Paste Special on another sheet and all is well. It is
frustrating not to get it to work properly but the data is most important.
Thanks,
Lee

 

[Lee]

I finally figured it out!
Instead of:
=SUMPRODUCT(--(CustomerNumberInvoice=Customers!$D3),--(WeekInvoice=$E$1),INVNET)
I changed to:
=SUMPRODUCT(--(CustomerNumberInvoice=$D3),--(WeekInvoice=$E$1),INVNET)
and it works and sorts like a charm. The worksheet is named Customers and I
don't know why Excel 2007 put it as part of the formula but deleting the
worksheet name from the formula all is well.
But the nagging question is why does this make it sort correctly and the
other way doesn't?
Thanks to all again.
Lee