Sorting and Sheet References

[drdreydel]

I'm using Excel 2007 and have come across something that I don't believe I ever saw in previous (non-ribbon) versions. Say I have 2 worksheets that I'm referencing in an if or sumif function. The function I'm writing is in Sheet1 but I reference some data in Sheet2 first when writing the function (i.e. I go to Sheet2, then come back to Sheet1 so Excel inserts a sheet reference to Sheet1) so that the result is something like this:

=IF(Sheet2!AG10='Sheet1'!E15,1,0)

I take this formula and drag it down a few cells in Sheet1 so we have this (showing formulas):
<TABLE style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=330><COLGROUP><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 3328" width=182><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 2706" width=148><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 137pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=182>Sheet1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=148>Sheet2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">=IF(Sheet2!AG7=Sheet1!E12,1,0)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">=IF(Sheet2!AG8=Sheet1!E13,1,0)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">=IF(Sheet2!AG9=Sheet1!E14,1,0)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">=IF(Sheet2!AG10=Sheet1!E15,1,0)</TD></TR></TBODY></TABLE>

Ok, now say I go to resort descending by the column Sheet1. My references for Sheet1! are not locked, so expect relative references to hold but the following happens:

<TABLE style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=330><COLGROUP><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 3328" width=182><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 2706" width=148><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 137pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=182>Sheet1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=148>Sheet2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">=IF(Sheet2!AG10=Sheet1!E15,1,0)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">=IF(Sheet2!AG9=Sheet1!E14,1,0)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">=IF(Sheet2!AG8=Sheet1!E13,1,0)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">=IF(Sheet2!AG7=Sheet1!E12,1,0)</TD></TR></TBODY></TABLE>

Instead of the first row still pulling data based on the "4" under Sheet1, it now pulls data based on the "1" in Sheet1 - i.e. behaving as if the reference was locked and the order of the data being referenced in Sheet1 is now flip flopped instead of staying relative to whatever is in the same row as the formula. Now, if I take off the reference for Sheet1 (so the formula reads like =IF(Sheet2!AG10=E15,1,0) the problem goes away. This just seems like a bug to me, and I don't recall ever having this problem with sorting in Excel 2003. Can anyone clue me in?

Thanks,

Andrey

 

[ZVI]

Hi Andrey,

This behavior is the same in Excel2003 and Excel2007.
To solve remove Sheet1! from the formula, that is,
instead of: =IF(Sheet2!AG7=Sheet1!E12,1,0)
use: =IF(Sheet2!AG7=E12,1,0)
or: =IF(E12 = Sheet2!AG7,1,0) - starting with E12 excludes entering of Sheet1 name
or even: =--(E12 = Sheet2!AG7)

Regards,

 

[drdreydel]

Yes I realize it is the same behavior (just checked myself) but this seems like quite a bug. Why does excel treat a same-sheet reference as being fixed even though it is clearly relative? Imagine that the formula requires going back and forth between Sheet1 and Sheet2 serveral times, so one would have to remove the Sheet1 reference in each instance in the formula. It's puzzling and worrying as I'm sure I've committed this mistake in the past and not realized it.

Thanks

 

[drdreydel]

Also, while writing an IF you could start on "same sheet" and avoid the Sheet1 reference, imagine writing something like a sumif where the first part of the syntax would require you to go to another sheet.

 

[ZVI]

Not sure if it's bug or feature. Reference through host Sheet means the reference through parent object and for me it looks native to be static.
May be it is useful in some exotic tasks, but I never used it.
Just remember the difference in behaviour at sorting to not get stuck