• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SumProduct: Sorting does not update relative references

C

Collock

New Member
I have this formual:


=SUMPRODUCT(--('Project Purchase Order and Sale'!$W$2:$W$5000='Detail EAC by Task Code'!$C7),--('Project Purchase Order and Sale'!$B$2:$B$5000='Detail EAC by Task Code'!V$2),'Project Purchase Order and Sale'!$Y$2:$Y$5000)


The problem I have is the relative references do not update when I sort the data to help me audit my model. Has anyone found a way around this including using diffrent functions? Or do some functions just not work properly when sorting ( I know sumif has this same problem)?
 
Hi Collock,


Not just some functions, practically all functions in Excel do not update references when you sort the data. A reference A1 written in B1 just refers to one cell to the left. It does not matter what value the cell contains due to sorting.


Please read http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/ to understand various referencing styles in Excel.
 
Hi Collock,


I have had a problem with sorting and formulae that was solved by taking out the sheet name form the formula of the sheet the formula is on.


So if you formula is on Sheet2 and in your formula you have a reference Sheet!A2, remove the Sheet! from the formula and simply leave it as A2.


kanti
 
Great tip, kchiba - thanks - it resolved the problem I was having trying to sort data where SUMPRODUCT formula was used. It seems that SUMPRODUCT does not maintain relative references, as desired, if the sheet name is included in the formula.
 
You must log in or register to reply here.
Back
Top