Formulas are not recalculating after sorting - Examples included?
Posted by Victoria on December 20, 2001 1:53 AM
I have created a spreadsheet for calculating Stock reconciliation and at present is sorted by product code. But if I try to change the sort criteria to sort by variance for example my formulas do not re-calculate according.
For example: =SUMIF(Deductions!$A$1:$A$1000,A4,Deductions!$B$1:$B$1000)
This would after sorting by the column Variance:
For Example: =SUMIF(Deductions!$A$1:$A$1000,A8,Deductions!$B$1:$B$1000)
Can you help!
Thank you in advance and Merry Christmas!
Posted by Chris D on December 20, 2001 4:04 AM
what's in A4 and A8 ?
Posted by Victoria on December 20, 2001 5:03 AM
A4 and A8 are product codes. The formula is looking up product codes from the customers information and seeing if it matchings the information in the main worksheet, if the product codes match then sum stock for each similar product code. A4 is just the moving product code in the main worksheet.
Posted by Chris D on December 20, 2001 5:57 AM
your formula looks like it is still looking at the same range to interrogate and the same range to sumif.....
this means that no matter what order your data in, the answers will always be the same
if you're trying to sumif by product code based on the variance, you need to change the second bit of your formula so that it looks at the range that holds your variances
unfortunately, merely changing the sort perameters will not automatically change the sumif conditions !!
did that make sense ?
Chris D
Posted by Chris D on December 20, 2001 6:05 AM
so if you want to sumif by variance, ie the same as what you're sorting by, you change the last bit of your formula to : D1:D1000 (where your variances are all in column D for example)
(One observation though, you are asking sumif to look at a cell (A4 and A8 etc) that is already within your range (A1 to A1000) - might be better to have a seperate list of product codes somewhere with the sumif formulae next to them ?)
hope this all helps
Chris
Posted by Victoria on December 20, 2001 6:33 AM
Thank you for your help and Merry Christmas again!
