Sorting in Excel 2007

[Lee]

I posted this back 2/7/09 and figured out how to make it work but don't know
why and was wondering why it does?

=SUMPRODUCT(--(CustomerNumberInvoice=Customers!$D3),--(WeekInvoice=$E$1),INVNET)
I changed to:
=SUMPRODUCT(--(CustomerNumberInvoice=$D3),--(WeekInvoice=$E$1),INVNET)

The worksheet is named Customers and contains the formulas with the customer
number in column D that it matches on the other sheets/dynamic ranges. When
I sort who purchased the most, the first formula puts the customer names in
column A in order but the formula gives the wrong dollar amounts. It returns
the amount of the customer that is now is the position that it used to be.
If customer #30 had the most and was in row 31, it sorts and puts #30 in the
second row (first 2 rows have headers) but pull info from what ever customer
is now in row 31. The formula has
=SUMPRODUCT(--(CustomerNumberInvoice=Customers!$D31),--(WeekInvoice=$E$1),INVNET)
in the second row instead of
=SUMPRODUCT(--(CustomerNumberInvoice=Customers!$D3),--(WeekInvoice=$E$1),INVNET)
When I deleted the "Customers" from the formula, all sorts properly.
Just wondering why it has to be this way?
Thanks,
Lee

 

[Eduardo]

Hi Lee,
The reason is because Customer is the name of the tab you are in, and you
are making reference to a cell D3, you only need to refer to the tab name if
you are in another tab
If this was helpful please say yes. Thank you

 

[Lee]

Thanks for the information.
Lee

Hi Lee,
The reason is because Customer is the name of the tab you are in, and you
are making reference to a cell D3, you only need to refer to the tab name
if
you are in another tab
If this was helpful please say yes. Thank you