Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting gives odd results
=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. -- Lee Coleman |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting gives odd results
Is there any gap (blank columns) between your table and sumproduct columns?
-- R. Khoshravan Please click "Yes" if it is helpful. "Lee" wrote: =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. -- Lee Coleman |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting gives odd results
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 "Khoshravan" wrote in message ... Is there any gap (blank columns) between your table and sumproduct columns? -- R. Khoshravan Please click "Yes" if it is helpful. "Lee" wrote: =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. -- Lee Coleman |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting gives odd results
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. -- Regards Roger Govier "Lee" wrote in message ... =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. -- Lee Coleman |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting gives odd results
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 "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... 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. -- Regards Roger Govier "Lee" wrote in message ... =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. -- Lee Coleman |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting gives odd results
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 "Lee" wrote in message ... =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. -- Lee Coleman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting and filtering results | Excel Worksheet Functions | |||
Sorting&Computing results | Excel Discussion (Misc queries) | |||
Sorting and displaying different results | New Users to Excel | |||
Sorting subtotal results | New Users to Excel | |||
getting accurate results when sorting | Excel Worksheet Functions |